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

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)

1 Like