# Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

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.

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.

###### 8 comments on “Tom’s Tutorials For Excel: Finding the Number Farthest From Zero”
1. Jeremy says:

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))}

• Tom Urtis says:

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.

2. JP says:

{=INDEX(\$B\$4:\$B\$15,MATCH(MAX(ABS(\$B\$4:\$B\$15)-0),ABS(\$B\$4:\$B\$15),0),1)}

• Tom Urtis says:

Another good one, thank you James. I was clearly behind the curve on this tutorial example.

3. Matthew Pritchett says:

I went without the array path. Does this work too? =IF(MAX(B4:B15)>ABS(MIN(B4:B15)),MAX(B4:B15),MIN(B4:B15))

• Matthew Pritchett says:

And if the positive is preferred. =IF(ABS(MIN(B4:B15))>MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))

• Tom Urtis says:

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))

• Tom Urtis says:

Yes, worked for me. I’ve made a note of that, thank you, Matt.