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