| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find closest match and return next highest number in range | Excel Discussion (Misc queries) | |||
| Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
| How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
| Find the closest match to a reference number in a row of unsorted | Excel Worksheet Functions | |||
| find closest match to a reference number in a row of numbers | Excel Discussion (Misc queries) | |||
Linear Mode
