| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Closest number match help ...
Looking for a way to find the number that falls closest to another number in
Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
|
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest number match help ...
Hi
try the followung array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Looking for a way to find the number that falls closest to another number in Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest number match help ...
Hi,
I addapted a formula from John Walkenbach Name A1-A3 Data Name A5 Target Enter this formula as an array (Shift-Ctrl-Enter =INDIRECT(ADDRESS(ROW(data)+MATCH(MIN(ABS(target-data)),ABS(target-data),0)-1,COLUMN(data)+0)) Note that if two numbers are the dame distance from your target it will select the first one Hope this helps "NP" wrote: Looking for a way to find the number that falls closest to another number in Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
|
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest number match help ...
Thanks for that. However, I forgot to also mention that I would like the
value returned to be higher. For example ... if A5 contains 800 then I would like the value returned in B5 to be 1500. Any ideas please? Many thanks, NP. "Frank Kabel" wrote in message ... Hi try the followung array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Looking for a way to find the number that falls closest to another number in Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
|
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest number match help ...
Hi
then try the following array formula =INDEX(A1:A4,MATCH(TRUE,A1:A4=800,0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Thanks for that. However, I forgot to also mention that I would like the value returned to be higher. For example ... if A5 contains 800 then I would like the value returned in B5 to be 1500. Any ideas please? Many thanks, NP. "Frank Kabel" wrote in message ... Hi try the followung array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Looking for a way to find the number that falls closest to another number in Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
|
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest number match help ...
Thats great. Many thanx.
NP "Frank Kabel" wrote in message ... Hi then try the following array formula =INDEX(A1:A4,MATCH(TRUE,A1:A4=800,0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Thanks for that. However, I forgot to also mention that I would like the value returned to be higher. For example ... if A5 contains 800 then I would like the value returned in B5 to be 1500. Any ideas please? Many thanks, NP. "Frank Kabel" wrote in message ... Hi try the followung array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0)) -- Regards Frank Kabel Frankfurt, Germany "NP" schrieb im Newsbeitrag ... Looking for a way to find the number that falls closest to another number in Excel ... Cell A1 contains value 750 Cell A2 contains value 1500 Cell A3 contains value 3000 When I enter a value in cell A5 (e.g. 800), the result I would like retuned in cell B5 is the number from cell a1, a2 or a3 which is closest to the value in A5. Anyone any ideas how to do this please? Many thanks, NP. |
| 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) | |||
| 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) | |||
| Closest number match help ... | Excel Worksheet Functions | |||
Linear Mode
