Find closest value to 0 in range of values in Excel (with non-numeric fields)

excel min absolute-value
Michael Mankus picture Michael Mankus · Dec 1, 2015 · Viewed 14.3k times · Source

I have a range of cells in Excel with various positive and negative numbers. The value of the cell is determined by an IF formula, and some of the cells in the range have no value ("").

I need to find the absolute value of the nearest cell to 0. So if I had a list of -10,-5,-1, 3, 6, and "": I need to get a result of 1.

I've tried multiple approaches but the fact that I have non-numeric cells seems to be an issue which is causing errors.

What I've tried:

{=Min(ABS(A1:D10))}, where I press Ctrl-Shift-Enter (returns an error because of non-numerics)

{=IF(MAX(D3:D22)<=0,-1,IF(MIN(ABS(D3:D22))=MIN(ABS(IF(D3:D22>0,D3:D22,MAX(D3:D22)))),1,-1))*MIN(ABS(D3:D22))}, where I press Ctrl-Shift-Enter (returns an error because of non-numerics)

{=IF(OR((D3:D22)>0),MIN(ABS(D3:D22)),-MIN(ABS(D3:D22)))}, where I press Ctrl-Shift-Enter (returns an error because of non-numerics)

Answer

Dirk Reichel picture Dirk Reichel · Dec 1, 2015 
=MIN(ABS(IF(ISNUMBER(D3:D22),D3:D22,9999)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter

should do the trick

or better use:

=INDEX(D3:D22,MATCH(MIN(IF(ISNUMBER(D3:D22),ABS(D3:D22))),ABS(D3:D22),0))

This is an array formula and must be confirmed with Ctrl+Shift+Enter

doesn't care about text or empty cells but having 0 as number will count... also negative numbers will be negative (if you allways want positive you need to ABS the whole formula...

Related questions

How to exclude 0 from MIN formula Excel

i need to know how can i exclude 0 from rows and get the MIN Value. But also i need to exlude the F1 Cell. Ex: A B C D E F 1 0 18 20 0 150 = 18 but if i do this In excel with =MIN(…

Read More
Using MIN/MAX in excel array formula

I have a simple array formula in excel that doesn't work in the way I wish. In columns A and B there is data (A1 is paired with B1 and so on) while in column F there is the calculation …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More