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
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:
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:
Thank you Jeremy!
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.