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)
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
Thanks for your comments, and for letting me know about the missing array formulas video. I’ve been doing a lot of work to expand my website and my Excel business services, and I must have messed up that link. I’ll look into it right away and send you a working link as soon as I fix it.
The video is viewable at this link:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-videos-explaining-array-formulas/
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))
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.
Thanks. A native formula is needed.
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)
Mighty cool. Thank you, Tom!
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.