Tom’s Tutorials For Excel: Finding the Number Closest to Zero
Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did, you could simply do a
VLOOKUP to find it), you can apply these array formulas as shown.
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.