# 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?

-

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.

-
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
This returns an error if your range also contains non-numeric data (e.g. text or formula errors) – CBRF23 Feb 6 '15 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.

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

-

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

-
(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 '15 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 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments. – Portland Runner Oct 6 '15 at 16:23