How can I set a cell's value if its neighboring cell is max in its range

I want to fill any cell in column B cell in column A (in same row) is =MAX(A1:A9). So if A4 = maximum of A1:A9, I want B4 to have the text value “Due”. So if A4 is the max of A1:A9 then B4 shows “Due” but other cells in B1:B9 are blank (*if their corresponding cell in A is not equal to maximum of A1:A9 also!)

I’m looking for a simple solution if it exists. I tried to use conditional formatting, such as Equals =MAX(A1:A9) for condition but I don’t see how to insert text as part of a Style.

I tried variations on using a formula in cells in B1:B9 such as =if($A1=MAX($A$1:$A$9), "Due", "") result is : #NAME?

and

=if($A1=MAX($A$1:$A$9), "Due") result is 0

and

=if(=MAX($A$1:$A$9), "Due", "") result is Err:501

and

=if(MAX($A$1:$A$9), "Due", "") result is Err:501

This just looks like it would be a common, simple thing to do in a spreadsheet.

Open the attached file and share the formula in B1. Thanks.
IF MAX Due LeroyG.ods (9.4 KB)

image

1 Like

Hallo
check if your use english Functionnames ⇒ Tools ⇒ Options ⇒ LO-Calc ⇒ Formula ⇒⇒ [x]use English-Functionnames
try your formula with semicolon instead Comma as Argument-delimiter

=IF($A1=MAX($A$1:$A$9) ; "Due" ; "")

Check whether column A contains numbers or text (your formula may not work for the latter)

2 Likes

Thanks for considering the issue! I did as you suggested, but it did not resolve the issue. It was working in other columns, so, in frustration, I deleted the non-working occurances of this formula and wrote it once again in the top affected cell, then dragged it down across the desired column, and voila, working! I’m not sure what the issue was; letter for letter I used the exact same formula…well, done with this for now, and another new skill added.