Tom’s Tutorials For Excel: Finding the Number Closest to Zero

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.

Array formula to return the number itself:
=INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))

Array formula to return the cell address:
=ADDRESS(MATCH(MIN(ABS(C7:C18)),ABS(C7:C18),0)+ROW(C7)-1,3)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
9 comments on “Tom’s Tutorials For Excel: Finding the Number Closest to Zero
  1. Rob Morrison says:

    The formula was very helpful. Thank you. I was trying to find the minimum number (the one closest to 0) from a group of numbers that were both + and – and I could not figure or find a formula to do it other than giving me the absolute number if it was negative. This worked. I am not sure how it works but it does. It would be great to try and understand the logic behind it.

    Also I went to your link to look at your video describing an Array and the video is no longer there. I would indeed like to watch it.

    Thanks again

  2. Scooter says:

    Can you help me get the same result above but in the case where the cells are not contiguous. As in: a1,b5 and f15 contain the values -1,2 and -5. The formula should return the value -1.

    I can’t figure out how to properly reference a1, b5 and f15 in place of c7:c20 in this formula:
    INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))

    • Tom Urtis says:

      Good question.
      Do you require a native formula such as the one I posted in this example, or is a User-Defined Function (containing VBA code) acceptable.

  3. Scooter says:

    Thanks. A native formula is needed.

    • Tom Ogilvy says:

      Scooter,
      For the specific question you asked, this would return -1

      =INDEX(CHOOSE({1,2,3},A1,B5,F15),MATCH(MIN(ABS(CHOOSE({1,2,3},A1,B5,F15))),ABS(CHOOSE({1,2,3},A1,B5,F15)),0))

      entered with Ctrl+Shift+Enter. You can adjust the CHOOSE({1,2,3},A1,B5,F15) (which replaces the C7:C20 in the original formula) to include more cells. For example if I wanted to add Z20 and AA4 I could use CHOOSE({1,2,3,4,5},A1,B5,F15,Z20,AA4). Each cell would need to be listed individually. so you could not have CHOOSE({1,2},A1,B5:B20)

  4. Scooter says:

    Thanks so much! That works perfectly, and I would never have figured that out. I didn’t even know you could use hard coded {}s in a formula. That formula is quite a mouthful … seems like Microsoft would just build a function to perform this task. Thanks again.

Leave a Reply

Your email address will not be published.

*