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

5 Answers 5

up vote 13 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
3  
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 '14 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 '14 at 2:23
1  
This returns an error if your range also contains non-numeric data (e.g. text or formula errors) –  CBRF23 Feb 6 at 15:41

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 works if your range also contains non-numeric data (e.g. text or formula errors) –  CBRF23 Feb 6 at 15:40

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

share|improve this answer
    
(1) As you know, this is not an answer to this question.  It is the answer to a different question.  We prefer to keep answers with the questions that they go with.  If you really want to post this answer, you might want to “ask” the corresponding question and then answer it.  (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.) –  G-Man Apr 22 at 20:56
    
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.”  So why post an answer that’s twice as long as the one he already has?  For that matter, why not say just =IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))? –  G-Man Apr 22 at 20:57

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.