Finding the Number Closest to Zero - KING OF EXCEL

Thursday, December 19, 2019

Finding the Number Closest to Zero

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

#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment