Choose number closest to zero

[jts2004]

a range of numbers positive & negative In A1:A5. I want the number closest to zero.


EX: -1, 8, 3, 17, -12 ans= -1

 

[Von Pookie]

Wouldn't just using =MIN(A1:A5) work?

 

[just_jon]

=MIN(ABS(A1:A5)

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

 

[jts2004]

min gives you -12.

 

[Domenic]

Try the following...

=INDEX(A1:A5,MATCH(MIN(ABS(0-A1:A5)),ABS(0-A1:A5),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Zack Barresse]

Hi,

Let's look at the following ...

Test Log.xls
	E	F	G	H	I	J
1	-1	8	3	17	-12	2
2
3	Closesttozero:	-1	-1
January

With standard formulas ...

=IF(ISNA(MATCH(MIN(IF(ABS(E1:J1)>0,ABS(E1:J1))),E1:J1,0)),-MIN(IF(ABS(E1:J1)>0,ABS(E1:J1))),MIN(IF(ABS(E1:J1)>0,ABS(E1:J1))))

which is confirmed with Ctrl + Shift + Enter, as it's an array formula.


Or if you download/install the morefunc add-in, it can be shortened to ...

=IF(ISNA(MATCH(SETV(MIN(IF(ABS(E1:J1)>0,ABS(E1:J1)))),E1:J1,0)),-GETV(),GETV())


Adjust ranges to suit.

HTH

 

[Aladin Akyurek]

=INDEX(A1:A5,MATCH(MIN(ABS(0-ABS(A1:A5))),ABS(0-ABS(A1:A5)),0))

which you need to confirm with control+shift+enter instead of just with enter.

As Domenic also shows, there is no need to put the range A1:A5 inside an ABS...

 

[Scott R]

=INDEX(A1:A5,MATCH(MIN(ABS(0-ABS(A1:A5))),ABS(0-ABS(A1:A5)),0))

which you need to confirm with control+shift+enter instead of just with enter.

As Domenic also shows, there is no need to put the range A1:A5 inside an ABS...

Why the double ABS in the MATCH arguments?
How about:
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5)),ABS(A1:A5),0))
Edit: with CSE entry

 

[jts2004]

Thanks everyone