| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find closest match to a reference number in a row of numbers
How can I find the closest match larger( or smaller) than a reference value
in a row of unsorted data |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find closest match to a reference number in a row of numbers
Hi!
Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: A5 = lookup_value =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0)) Biff "Nick Krill" <Nick wrote in message ... How can I find the closest match larger( or smaller) than a reference value in a row of unsorted data |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find closest match to a reference number in a row of numbers
Biff:
This is very nice. Can you explain or interpret how the formula is treating or bringing back the both the match(row) and the match(column) arguments of the index function? Confused here... TIA,, "Biff" wrote in message ... Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: A5 = lookup_value =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0)) Biff "Nick Krill" <Nick wrote in message ... How can I find the closest match larger( or smaller) than a reference value in a row of unsorted data |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find closest match to a reference number in a row of numbers
Hi!
It's only referencing a single (row) array so there is no column argument. Simply determine the minimum deviation from the lookup_value. Since the OP stated that it could be either above or below the lookup_value we have to use the ABS function so that negative deviations are made equal to positive deviations. Biff "JMay" wrote in message news:hnSpf.61035$WH.17922@dukeread01... Biff: This is very nice. Can you explain or interpret how the formula is treating or bringing back the both the match(row) and the match(column) arguments of the index function? Confused here... TIA,, "Biff" wrote in message ... Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: A5 = lookup_value =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0)) Biff "Nick Krill" <Nick wrote in message ... How can I find the closest match larger( or smaller) than a reference value in a row of unsorted data |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find closest match to a reference number in a row of numbers
Thanks for the clarification Biff;
Jim "Biff" wrote: Hi! It's only referencing a single (row) array so there is no column argument. Simply determine the minimum deviation from the lookup_value. Since the OP stated that it could be either above or below the lookup_value we have to use the ABS function so that negative deviations are made equal to positive deviations. Biff "JMay" wrote in message news:hnSpf.61035$WH.17922@dukeread01... Biff: This is very nice. Can you explain or interpret how the formula is treating or bringing back the both the match(row) and the match(column) arguments of the index function? Confused here... TIA,, "Biff" wrote in message ... Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: A5 = lookup_value =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0)) Biff "Nick Krill" <Nick wrote in message ... How can I find the closest match larger( or smaller) than a reference value in a row of unsorted data |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
| Find similar numbers from two columns of numbers | Excel Discussion (Misc queries) | |||
| Match Closest Results from Data Array | Excel Discussion (Misc queries) | |||
| How can I compare a number against a list of numbers | Excel Worksheet Functions | |||
| two columns of numbers, need to sort/filter to find one number th. | Excel Discussion (Misc queries) | |||
Hybrid Mode
