Tom’s Tutorials For Excel: Finding the Number Farthest From Zero
Sometimes you’ll need to find a number in a list that is farthest from zero, where some numbers might be positive and others might be negative.
This task has 2 considerations:
1. You will need more than a simple MIN or MAX function.
2. You will want to decide how to display the number, either as…
(a) …its raw actual appearance in the list, be it positive or negative, or
(b) …its absolute representation as a positive-looking number even if that raw number is negative.
The first picture shows the raw actual number of -22 in cell E3, with this array formula:
=IF(AND(MIN(B4:B15)<0,MIN(ABS(B4:B15)))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))
EDIT ON JUNE 21, 2013:
See Jeremy’s comment below, in which he found my formula in cell E3 to be in error.
Jeremy’s formula, which is correct and which you should use instead of mine, is this array:
=IF(ABS(MIN(B4:B15))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))
Thank you Jeremy!
– Tom
In the next picture, the absolute number 22 is returned in cell H3 with this array formula:
=MAX(ABS(B4:B15))
Note, these formulas are both array formulas. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.
If you are unfamiliar with array formulas, see my video and explanation of arrays here.
I believe that formula is wrong for cell E3. No matter how big a positive number you put in you will always get the smallest number. I think you are after the following formula for cell E3 in the above example.
={IF(ABS(MIN(B4:B15))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))}
Jeremy: Thank you very much for correcting my error. You are absolutely right. I posted your formula as an edit to this tutorial. Thanks again.
{=INDEX($B$4:$B$15,MATCH(MAX(ABS($B$4:$B$15)-0),ABS($B$4:$B$15),0),1)}
Another good one, thank you James. I was clearly behind the curve on this tutorial example.
I went without the array path. Does this work too? =IF(MAX(B4:B15)>ABS(MIN(B4:B15)),MAX(B4:B15),MIN(B4:B15))
And if the positive is preferred. =IF(ABS(MIN(B4:B15))>MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))
I would think that if a positive number is preferred, as simple MAX function would do. To consider the possibility of there being only negative numbers, you could do
=MAX(0,MAX(B4:B15))
Yes, worked for me. I’ve made a note of that, thank you, Matt.