Is there a function in Excel to find the maximum absolute value of a range?

Question

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?

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

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

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

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

Answer 5

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

This will return the largest magnitude number while preserving the sign.

Answer 6

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

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

Answer 7

=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))

Answer 8

As of Office 365, the version posted by the OP works.

= MAX(ABS(A1:A10))

You do NOT have to use array mode.

Answer 9

I needed to find the max and min deviation from zero, irrespective of the sign. However, I found no way to do that using standard Excel functions when both positive and negative numbers are involved. So, I created my own VBA functions (based on code I found elsewhere) that works every time, even when some of the values are null or strings (non-numeric).

Here are the two functions MAX_ABS(range) and MIN_ABS(range, [optional boolean] allowZeros)

MAX_ABS is simple and straightforward:

Function MAX_ABS(SelectRng As Range) As Double
    Dim result As Double
    Dim work As Double
    result = 0
    
    myarray = SelectRng.Value
    
    For i = 1 To UBound(myarray, 1)
        For j = 1 To UBound(myarray, 2)
            If IsNumeric(myarray(i, j)) = True Then     'Numbers Only
                work = VBA.Abs(myarray(i, j))
                If work > VBA.Abs(result) Then          'Test
                    result = myarray(i, j)              'Save new max
                End If
            End If
        Next
    Next
    
    MAX_ABS = result
    
End Function

MIN_ABS is a bit trickier because if you initialize the return value to zero then it will always return zero, so you need to work around that. So I use an optional boolean parameter to allow zeros or ignore them.

Function MIN_ABS(SelectRng As Range, Optional allowZeros As Boolean = False) As Double
    Dim work As Double
    Dim result As Double
    Dim firstHit As Boolean
    result = 0
    firstHit = True
    
    myarray = SelectRng.Value
    
    For i = 1 To UBound(myarray, 1)
        For j = 1 To UBound(myarray, 2)
        
             If IsNumeric(myarray(i, j)) = True Then                'Numbers Only
                work = VBA.Abs(myarray(i, j))
                
                'Zeros allowed
                If allowZeros = True Then
                    If firstHit = True Then                         'First pass -- Initialize
                        firstHit = False
                        result = myarray(i, j)                      'Save first value
                        
                    ElseIf work < VBA.Abs(result) Then              'Subsequent pass -- Test
                        result = myarray(i, j)                      'Save new min
                    End If
                    If work = 0 Then
                        GoTo getout                                 'Zero -- no reason for further tests
                    End If
                    
                'Zeros not allowed
                ElseIf result = 0 And work > 0 Then                 'First pass -- Initialize
                    result = myarray(i, j)
                ElseIf work > 0 And work < VBA.Abs(result) Then     'Subsequent pass -- Test
                    result = myarray(i, j)
                End If
            End If
        Next
    Next
    
getout:
    
    MIN_ABS = result

End Function

Here are the results of the functions on a list of values that includes numbers, strings, and blanks.

Results of MAX_ABS and MIN_ABS

Answer 10

=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))