CODEWITHSUNDEEP
excelminabsolute-value
Michael Mankus
Michael Mankus

Reputation: 4778

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

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)

Upvotes: 1

Views: 23942

Answers (4)

Scooter
Scooter

Reputation: 1

Here is how one finds the number closest to zero when the values are not in contiguous cells? (i.e., not in a 'range') For example, a1, b5 and f15 contain the values -3, 4 and -5. The formula should return the value -3.

Tom Ogilvy at https://www.atlaspm.com/toms-tutorials-for-excel/ answered the question:

For the specific question you asked, this would return -3

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

Upvotes: 0

Michael Mankus
Michael Mankus

Reputation: 4778

I'm posting this only because it worked for me before the other users came in with their answers. I'll still accept one of their answers rather than this.

=MIN(IF(D3:D22>0,D3:D22,ABS(D3:D22)))

And of course I had to press Ctrl+Shift+Enter to get this to work.

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

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

Upvotes: 5

John Coleman
John Coleman

Reputation: 51998

If you have an upper bound, say 10^9, on what the min is you can do:

{=MIN(ABS(IF(ISNUMBER(D3:D22),D3:D22,10^9)))}

If you find it problematic to make any assumptions about what the min is, note that it is certainly less than 1 + the absolute value of the maximum number, so you could use:

{=MIN(ABS(IF(ISNUMBER(D3:D22),D3:D22,1+ABS(MAX(D3:D22)))))}

Upvotes: 1

Related Questions