Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the closest match to a reference number in a row of unsorted
Data is random in nature and unsorted. Generally between 10 and 100, two
decimal precision (see below). How do I find the position of the entry closest a given value, say 45.11 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the closest match to a reference number in a row of unsorted
With your data in A1:K1:
=MATCH(MIN(A1:K1-45.11),A1:K1-45.11,0) Array-entered, which means commit with CTRL+SHIFT+ENTER instead of just with ENTER In the formula bar, the formula will appear in curly brackets, to prove it is an array formula -- Kind regards, Niek Otten "Nick Krill" wrote in message ... Data is random in nature and unsorted. Generally between 10 and 100, two decimal precision (see below). How do I find the position of the entry closest a given value, say 45.11 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the closest match to a reference number in a row of unsorted
Neik,
Your formula returns the position of the lowest number for me even if one of the given numbers is 45.11. Should the range subtractions not be enclosed in ABS() functions as in: =MATCH(MIN(ABS(A1:K1-45.11)),ABS(A1:K1-45.11),0) -- Regards Sandy with @tiscali.co.uk "Niek Otten" wrote in message ... With your data in A1:K1: =MATCH(MIN(A1:K1-45.11),A1:K1-45.11,0) Array-entered, which means commit with CTRL+SHIFT+ENTER instead of just with ENTER In the formula bar, the formula will appear in curly brackets, to prove it is an array formula -- Kind regards, Niek Otten "Nick Krill" wrote in message ... Data is random in nature and unsorted. Generally between 10 and 100, two decimal precision (see below). How do I find the position of the entry closest a given value, say 45.11 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and match totals Genius Req. this should be easier to read | Excel Worksheet Functions | |||
Match Closest Results from Data Array | Excel Discussion (Misc queries) | |||
Match two tables using unique ID number | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
Find all cells with a number and mulitply | Excel Discussion (Misc queries) |