lookup and forecast a value in a range

[sunilbalguri]

can anyone help me with the formula for looking up and forecasting in a range

A	B
0.0	0.000
0.1	0.021
0.2	0.088
0.3	0.196
0.4	0.337
0.5	0.500
0.6	0.671
0.7	0.838
0.8	0.968
0.9	1.066
1.0	1.000

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

example: how to find the value for 0.43 in column A

Sunil

 

[vds1]

Something like this ?

C2 = 0.43

=VLOOKUP(ROUNDDOWN(C2,1),A:B,2,0)

o/p: 0.337

or

=VLOOKUP(ROUNDUP(C2,1),A:B,2,0)

o/p : 0.5

 

[sunilbalguri]

Thank you for the reply, i would like to have an answer in between 0.337 and 0.5 by forecasting the values between 0.4 and 0.5 of column A

 

[vds1]

Try,

Assume,

C2 = 0.43

then

=((VLOOKUP(ROUNDUP(C2,1),A:B,2,0)*VLOOKUP(ROUNDDOWN(C2,1),A:B,2,0))*C2)/(ROUNDUP(C2,1) *ROUNDDOWN(C2,1))

o/p : 0.362275

Hope this helps!

 

[vds1]

Sorry .. My solution is wrong. I will let you know if i find something correct.

 

[Andrew Poulsom]

How about?

=FORECAST(0.43,INDEX(B1:B11,MATCH(C1,A1:A11)):INDEX(B1:B11,MATCH(C1,A1:A11)+1),INDEX(A1:A11,MATCH(C1,A1:A11)):INDEX(A1:A11,MATCH(C1,A1:A11)+1))

 

[sunilbalguri]

How about?

=FORECAST(0.43,INDEX(B1:B11,MATCH(C1,A1:A11)):INDEX(B1:B11,MATCH(C1,A1:A11)+1),INDEX(A1:A11,MATCH(C1,A1:A11)):INDEX(A1:A11,MATCH(C1,A1:A11)+1))

Thanks a lot.. its working.