comparing a number to a list of numbers

M

mr_nice!

I have a column of numbers and a single number in a seperate cell.

I would like to know what functions I could use where I compare th
single number to the column of numbers and return that number whic
closest.

i.e. compare 14.5

1.5
1.75
1.9
2
5
10
15
17.5

I want it to return 15 as it is the closest number

any ideas

nic
 
B

Bob Phillips

Sort the data descending and use

=INDEX(A1:A8,MATCH(E1,A1:A8,-1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
M

Michel Bru

single number to the column of numbers and return that number which closest.
i.e. compare 14.5

1.5
1.75
1.9
2
5
10
15
17.5
Click to expand...

Hi nice,

If compare with 12.5 and .... return which number 10 or 15. Closest?

Best regards.

Michel BRUYÈRE
 
D

Dav

Something like

=IF(E1-INDEX(a1:a8,MATCH(E1,a1:a8,1))<INDEX(a1:a8,MATCH(E1,a1:a8,1)+1)-E1,INDEX(a1:a8,MATCH(E1,a1:a8,1)),INDEX(a1:a8,MATCH(E1,a1:a8,1)+1))

But this will round up in the case of being equidistant, you may want
it to round down. The data must be sorted ascending

Regards

Dav
 
B

Bob Phillips

Simpler

=INDEX(A1:A8,MATCH(MIN(ABS(A1:A8-E1)),ABS(A1:A8-E1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top