Formula Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Try

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

--

Regards,

Peo Sjoblom

Joerolla said:
I have a range in column A (1-20), B1 = 5.2, How do I write formula so
Click to expand...
that C1 will give the closest answer to B1 from range in Column A?
 
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

Joerolla said:
I have a range in column A (1-20), B1 = 5.2, How do I write formula so
Click to expand...
that C1 will give the closest answer to B1 from range in Column A?
 
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.
 
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.
 
Back
Top