Function to find closest to 0 (including neg #'s) in cloumn

[Dante]

I am trying to find a function that will sort out numbers in a column (pos &
neg) and tell me wich one is closest to zero.

 

[Bob Phillips]

=MIN(IF(F1:F20<>0,ABS(F1:F20)))*SIGN(IF(F1:F20<>0,F1:F20))

which is an array formula, so commit with Ctrl-Shift-Enter

 

[Gary''s Student]

Bob: How about if zero is in the column?

 

[Dave Peterson]

One more:

=INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A30)),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[Biff]

=INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A30)),0))

Doesn't account for zero.

Just a slight tweak:

=INDEX(A1:A20,MATCH(TRUE,ABS(A1:A20)=MIN(IF(ABS(A1:A20)>0,ABS(A1:A20))),0))

Biff

 

[Biff]

How about if zero is in the column?

Bob's formula excludes zero, but using the sample data below will return 8.
Should return -8.

0
98
-8
-14
10

Tweaked Dave's formula to:

=INDEX(F1:F20,MATCH(MIN(IF(ABS(F1:F20)>0,ABS(F1:F20))),ABS(F1:F20),0))

Biff

 

[Biff]

Just a slight tweak:

=INDEX(A1:A20,MATCH(TRUE,ABS(A1:A20)=MIN(IF(ABS(A1:A20)>0,ABS(A1:A20))),0))

And, just a slight tweak to the slight tweak! <g>

=INDEX(A1:A20,MATCH(MIN(IF(ABS(A1:A20)>0,ABS(A1:A20))),ABS(A1:A20),0))

Biff

=INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A30)),0))

Doesn't account for zero.

Just a slight tweak:

=INDEX(A1:A20,MATCH(TRUE,ABS(A1:A20)=MIN(IF(ABS(A1:A20)>0,ABS(A1:A20))),0))

Biff

 

[Dave Peterson]

In my head, 0 is the closest to 0.

I'm not sure if the OP wants the closest non-zero to zero or what.

Are you positive that the OP wanted it that way? <vbg>

(but thanks for the tweaks no matter which way the OP meant.)

 

[Biff]

Are you positive that the OP wanted it that way? <vbg>

I'm never sure of anything! (unless I see it in front of my face)

Biff

 

[Dave Peterson]

Two mathematicians were traveling on a train.

One says to the other: It appears that those sheep have just been shorn.

The other replies: Well, at least on the side facing us.

Are you positive that the OP wanted it that way? <vbg>

I'm never sure of anything! (unless I see it in front of my face)

Biff