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

4 Answers 4

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
    
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying. –  Pedro77 May 7 at 17:30

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

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

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).
share|improve this answer

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.