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.

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
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))}

  2. JP says:

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*