Formula Question

[Guest]

I have a range in column A (1-20), B1 = 5.2, How do I write formula so that C1 will give the closest answer to B1 from range in Column A?

 

[Peo Sjoblom]

Try

=VLOOKUP(ROUND(B1,0),A1:A20,1,0)

--

Regards,

Peo Sjoblom

I have a range in column A (1-20), B1 = 5.2, How do I write formula so

that C1 will give the closest answer to B1 from range in Column A?

 

[Vasant Nanavati]

Try:

=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-B1)),ABS(A1:A20-B1),0))

entered as an array formula with <Ctrl> <Shift> <Enter>.

--

Vasant

I have a range in column A (1-20), B1 = 5.2, How do I write formula so

that C1 will give the closest answer to B1 from range in Column A?

 

[Vasant Nanavati]

Peo, will that give the *closest * answer?

 

[Guest]

Thanks V N

 

[Peo Sjoblom]

I assumed all values in A1:A20 were positive integers,
of course I probably mistook 1-20 as meaning just that
and not only A1:A20.

 

[Vasant Nanavati]

Hi Peo:

You could be right. I assumed it was rows 1-20 but on re-reading it's not
clear. Anyway, now the OP has 2 answers! <g>

Regards,

Vasant.