Excel: how to highlight the max value of each row only if the maximum value is a certain value larger than the min value?

Question

I want to highlight the maximum value of a cell if that maximum value is larger than the minimum value by 5.

68.3 64.3 62.6 65.5 66.3 75.9

So in this case, 75.9 (max val) - 62.6 (min value) >=5 so the max value should be highlighted.

I tried using conditional formatting with this

=max(if (max(A1:F1)-min(A1-F1)>=5, 1,0))

and

=max(A1:F1)-min(A1:F1)>=5

which highlighted all the cells..

So, any thoughts on how to do this?

Thanks in advance.

Answer 1

To highlight just the maximum value, given that it is at least 5 greater than the minimum, try

=AND(A2=MAX($A2:$F2),A2>=MIN($A2:$F2)+5)

where the range starts at A2.

Note that this uses absolute addressing for the range of columns used to calculate Max and Min ($A2:$F2), but relative addressing for the rows ($A2:$F2). If the formula is applied to row 3 (say), this will automatically change to

=AND(A3=MAX($A3:$F3),A3>=MIN($A3:$F3)+5)

---

Here is an example where the formula is applied to two rows - the formula is exactly the same, only the 'Applies to' section is changed: