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 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 at 2:23

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 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).
-