G
Guest
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.
neg) and tell me wich one is closest to zero.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
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
which is an array formula, so commit with Ctrl-Shift-Enter
G
Guest
Bob: How about if zero is in the column?
D
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.
=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.
B
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
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
B
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
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
B
Biff
Just a slight tweak:
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: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
Biff said:=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
D
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.)
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.)
B
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
I'm never sure of anything! (unless I see it in front of my face)
Biff
D
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.
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.