Conditionally format maximum value only if unique?

English
#1

I am using the following formula to highlight the maximum value from a range of cells:

AND($Sheet1.G4=MAX($G4,$O4,$W4,$AE4,$AM4,$AU4,$BC4,$BK4,$BS4,$CA4,$CI4,$CQ4,$CY4,$DG4,$DO4,$DW4,$EE4,$EM4,$EU4,$FC4,$FK4),G4>H4)

This formula is correct and does exactly what I want except for one thing. I want it to highlight the maximum IF THE MAXIMUM IS UNIQUE.

That is, if there are no other cells in that range which are equal to the maximum.

I am sure something can be added to the formula above to do this but I am not sure what it is. Please help.

#2

Please try
AND($Sheet1.G4=MAX($G4,$O4,$W4,$AE4,$AM4,$AU4,$BC4,$BK4,$BS4,$CA4,$CI4,$CQ4,$CY4,$DG4,$DO4,$DW4,$EE4,$EM4,$EU4,$FC4,$FK4),G4>H4,COUNTIF($G4~$O4~$W4~$AE4~$AM4~$AU4~$BC4~$BK4~$BS4~$CA4~$CI4~$CQ4~$CY4~$DG4~$DO4~$DW4~$EE4~$EM4~$EU4~$FC4~$FK4, MAX($G4,$O4,$W4,$AE4,$AM4,$AU4,$BC4,$BK4,$BS4,$CA4,$CI4,$CQ4,$CY4,$DG4,$DO4,$DW4,$EE4,$EM4,$EU4,$FC4,$FK4)-1)
It will work, but it’s impossible to read, understand and (if necessary) change

#3

Perhaps, knowing how the table is structured, could be possible to simplify a bit the formula, with MAXIFS.
A sample file would be helpful.

#4

Thanks for your response.

I’m not sure what you meant by the last part (“it’s impossible to…”), but anyway this does not work.

This formula has removed some conditional formatting even though the formatting was correct, and still highlights some maximums which are not unique.

#5

The formula is not complicated, but the cells it applies to are not next to each other so I have to specify each individually, which is why it is very long.

I want conditional formatting to apply to the maximum of the specified cells, but ONLY IF:

  1. The maximum is unique (no other cells in the range are equal to it)
  2. The maximum is bigger than the cell value next to it (G4>H4 in my formula above)
#6

Try to replace the

at the end with =1)

Let’s assume the formula would leave out cell $EU4 - how long would it take to notice this?
On some “maintenance”-jobs I decided it is easier to redo everything, than to find the place, where somebody missed a cell.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.