Highlight max absolute value in a row of data
I'm trying to use conditional formatting to highlight the max absolute value within a row of data. For example, if my data is 10, -11, 5, -2
, I want -11
to be highlighted to signify the largest movement from zero. Any idea how to accomplish this?
I have tried using =MAX(ABS(range))
and modifying it to highlight the desired number, no luck yet. I have also tried using Ctrl+Shift+Enter to make it an array within conditional formatting, but no luck there either.
Thanks!
Solution 1:
Conditional formatting does not like CSE array formulas.
Also you need the formula to resolve to TRUE/FALSE and not return a number.
Use a formula like this:
=AGGREGATE(14,6,ABS($A$1:$A$4),1)=ABS($A1)
Solution 2:
You can calculate the maximum absolute value without an array formula. For data in A1:A10,
=ABS(A1)=MAX(-MIN($A$1:$A$10),MAX($A$1:$A$10))
The maximum absolute value will be the larger of the minimum value times -1 or the maximum value.
Credit to Julie for the idea in this old answer: https://superuser.com/a/641624/76571