# Is there a function in excel to find the max absolute value of a range?

I'm looking for a function in Excel that looks something like

= MAX(ABS(A1:A10))

Except ABS() doesn't take a range of numbers.

The best that I can come up with is:

= MAX(ABS(MIN(A1:A10),ABS(MAX(A1:A10)))

It does the trick, but it's messy as all heck and I can't believe there's not a better way. Any ideas?

-

You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.

-
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P –  Ben Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying. –  Pedro77 May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient. –  sancho.s Nov 20 '14 at 2:23
This returns an error if your range also contains non-numeric data (e.g. text or formula errors) –  CBRF23 Feb 6 at 15:41

I don't like arrays so I would use the following:

=MAX(-MIN(range), MAX(range))

This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.

-
This works if your range also contains non-numeric data (e.g. text or formula errors) –  CBRF23 Feb 6 at 15:40

This VBA solution works too.

Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers

Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)

End Function
1. Open your VBA editor (Alt+F11)
2. Insert a new module on the right pane
3. Copy & paste the code to the module
4. Go back to Excel and use =absMax(A1:A3)

-

Try this formula (from here)

=MAX(INDEX(ABS(A1:A10),0,1))

It combines:

• The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
• Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
-

=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10))

This will find the absolute max value out of the range but still return the actual value with respective sign (+/-) and not the absolute value.

-