I have the following problem with my excel table. Maybe you can help.

This is the table I have:

A B

33 5.5462
62 5.5556
90 5.5524
182 5.5220

column A represent a number of days and B interest rate values.

I'm looking for a formula that will return the interest rate of any
given day that is in between 33 and 182.
So if the number of days is 33, it will return 5.5462.
But if it is 40, the formula will find the interest rate using
interpolation
from the closet values (the formula knows that 40 is between 33 and 62
therefore it will use 33, 62, 5.5462 and 5.5556). The same holds for 75
for example (62, 90, 5.5556 and 5.5524)
I know how to make the interpolation work I just need the formula for
the reference.

Thanks.

It works fine except for the last value for which it returns #DIV/0!

So in my example, if I enter 182 I get in error...

Thanks,

Amir


Alok wrote:
Hi
I am giving you a non-VBA answer and see if it works for you.
You can use the formula

=FORECAST(A6,OFFSET(B1:B2,MATCH(A6,A1:A4,1)-1,0),OFFSET(A1:A2,MATCH(A6,A1:A4,1)-1,0))

This assumes that your x values are in A1 to A4 in ascending order. The
corresponding y values are in B1 to B4 and the x value for which you want to
find the corresponding y value is in A6.

Alok

"amirstal" wrote:

I have the following problem with my excel table. Maybe you can help.

This is the table I have:

A B

33 5.5462
62 5.5556
90 5.5524
182 5.5220

column A represent a number of days and B interest rate values.

I'm looking for a formula that will return the interest rate of any
given day that is in between 33 and 182.
So if the number of days is 33, it will return 5.5462.
But if it is 40, the formula will find the interest rate using
interpolation
from the closet values (the formula knows that 40 is between 33 and 62
therefore it will use 33, 62, 5.5462 and 5.5556). The same holds for 75
for example (62, 90, 5.5556 and 5.5524)
I know how to make the interpolation work I just need the formula for
the reference.

Thanks.