Is there a function in Excel to find the maximum 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?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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

Solution 4:

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)