Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 11 down vote accepted

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.

share|improve this answer
1  
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
add comment

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.

share|improve this answer
add comment

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)

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.