| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
=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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
=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 |
|
#6
|
|||
|
|||
|
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. |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES | Excel Worksheet Functions | |||
| How do I create an equivalent VLOOKUP function using FIND? | Excel Worksheet Functions | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| Help with the FIND function | Excel Worksheet Functions | |||
| HOW TO USE FIND OR SEARCH FUNCTION TO FILL DATA | Excel Worksheet Functions | |||
Linear Mode
