Find the closest match to a reference number in a row of unsorted

G

Guest

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
 
N

Niek Otten

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
 
S

Sandy Mann

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Step through a row of data 1
Locate first number that exceeds reference value 2
find closest match to a reference number in a row of numbers 4
How do I find the closest number to a given number in an unsorted. 4
Need to find a row in a worksheet based on the value of a cell 1

Top