comparing a number to a list of numbers

  • Thread starter Thread starter mr_nice!
  • Start date Start date
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
 
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)
 
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
 
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
 
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)
 
Back
Top