Find number closest to 0
 

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.

Find number closest to 0
 

On Thu, 16 Oct 2008 17:03:07 -0700, snappertime
wrote:

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.


Enter this as an **array** formula:

=INDEX(chainage,MATCH(MIN(IF(ISNUMBER(chainage),of fset)),offset,0))

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
If you do it correctly, Excel will place braces {...} around the formula.
--ron

Find number closest to 0
 

Thanks mate that works

"Ron Rosenfeld" wrote:

On Thu, 16 Oct 2008 17:03:07 -0700, snappertime
wrote:

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.


Enter this as an **array** formula:

=INDEX(chainage,MATCH(MIN(IF(ISNUMBER(chainage),of fset)),offset,0))

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
If you do it correctly, Excel will place braces {...} around the formula.
--ron

Find number closest to 0
 

On Thu, 16 Oct 2008 18:26:01 -0700, snappertime
wrote:

Thanks mate that works

"Ron Rosenfeld" wrote:

On Thu, 16 Oct 2008 17:03:07 -0700, snappertime
wrote:

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.


Enter this as an **array** formula:

=INDEX(chainage,MATCH(MIN(IF(ISNUMBER(chainage),of fset)),offset,0))

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
If you do it correctly, Excel will place braces {...} around the formula.
--ron


You're welcome. Glad to help.
Thanks for the feedback.
--ron