# Choose number closest to zero

#### jts2004

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

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Wouldn't just using =MIN(A1:A5) work?

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

min gives you -12.

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!

Hi,

Let's look at the following ...
Test Log.xls
EFGHIJ
1-18317-122
2
3Closesttozero:-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

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

=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?
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5)),ABS(A1:A5),0))
Edit: with CSE entry

Thanks everyone

Replies
6
Views
211
Replies
2
Views
177
Replies
1
Views
114
Replies
4
Views
173
Replies
6
Views
219

1,203,675
Messages
6,056,683
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back