|
Function to find closest to 0 (including neg #'s) in cloumn
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. |
=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 -- HTH Bob Phillips "Dante" wrote in message ... 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: How about if zero is in the column?
-- Gary''s Student "Bob Phillips" wrote: =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 -- HTH Bob Phillips "Dante" wrote in message ... 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. |
One mo
=INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- Dave Peterson |
=INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A 30)),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" wrote in message ... One mo =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- Dave Peterson |
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:F2 0))),ABS(F1:F20),0)) Biff "Gary''s Student" wrote in message ... Bob: How about if zero is in the column? -- Gary''s Student "Bob Phillips" wrote: =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 -- HTH Bob Phillips "Dante" wrote in message ... 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. |
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:A2 0))),ABS(A1:A20),0)) Biff "Biff" wrote in message ... =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A 30)),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" wrote in message ... One mo =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- 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 wrote: =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A 30)),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" wrote in message ... One mo =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- Dave Peterson -- Dave Peterson |
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" wrote in message ... 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 wrote: =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A 30)),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" wrote in message ... One mo =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- Dave Peterson -- 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. Biff wrote: 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" wrote in message ... 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 wrote: =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A 30)),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" wrote in message ... One mo =INDEX(A1:A30,MATCH(TRUE,ABS(A1:A30)=MIN(ABS(A1:A3 0)),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. Dante wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 03:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com