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

Is there a way to do this calculation from a different number say 7.5? I need something that will tell me if 2.5 on a string of numbers is farther away from 7.5 than 10 for instance.

Can you not accomplish this by using the example? If not, please explain with a more specific example and why this is needed.