Liner interpolation

Hello everyone,

Its simple linear interpolation but IF function has its limitation. I want to be able to automate this with an excel function or a combination of functions so im only using two cells, input and output. Please adivse.

C D
2 Input tenor : 435
3 Output rate 14.38%
4
5
6 TENOR RATE
7 7 14.39%
8 15 14.06%
9 30 14.08%
10 60 14.10%
11 90 14.09%
12 120 14.00%
13 180 13.98%
14 270 14.12%
15 365 14.36%
16 730 14.48%
17 1095 14.46%
18 1460 14.49%
19 1825 14.60%
20 2190 14.64%
21 2555 14.94%
22 2920 14.97%
23 3285 15.06%
24 3650 15.12%
25 5475 15.27%
26 7300 15.40%

input can be any number
if input is <= 365, output should be the appropriate rate

Example
INPUT OUTPUT
1,2,3,4,5,6 or 7 14.39%
input >270 and <= 365 14.36%

If input is greater than 365, out put will be interpolated rate will be
=D15+((D16-D15)*(D2-C15)/(C16-C15))
this formula goes in cell D3, answer is 14.38%

THANKYOU!!

If input is greater than 365, out put will be interpolated rate will be
=D15+((D16-D15)*(D2-C15)/(C16-C15))
this formula goes in cell D3, answer is 14.38%

IMO, the easiest way to do this kind of thing is to use Quattro Pro (or other spreadsheet) that has a built in linear interpolation function. I know this is an Excel forum, and not everyone has access to other spreadsheets. If you can get access to something with a built in linear interpolation function, you might find that is the easiest.

One of the first things I did when I went from QP to Excel was to write my own linear interpolation UDF. If you're familiar with VBA and writing UDF's, this isn't difficult.

Linear interpolation broken down into steps:
step 1): Locate the interval in the source table that contains "x". With native Excel functions, this is probably the hardest part. If you can allow yourself 4 extra cells, it really isn't difficult. A fairly simple combination of four INDEX(MATCH()) functions will extract the boundary conditions from the source table. Once that secondary table is built, then TREND or FORECAST can easily be used to do the interpolation. The real difficulty comes in when you want, as you've requested, to make it a single cell formula, without the intermediate cells. I know it can be done, but the resulting formula is very long and unweildy.

If done in VBA, this step is pretty easy using the MATCH function

step 2): perform the interpolation using either the TREND function or the formula as you posted it. Once the interval is located, this is almost trivial.

1,2,3,4,5,6 or 7 14.39%
input >270 and <= 365 14.36%

It seems that for values less than 365, you don't want to interpolate. This suggests that you simply want to extract values from the table rather than interpolate. This should be fairly easy using the INDEX(MATCH()+1) combination nested in an IF function to handle those values less than 7. IF(x<=7,INDEX(sourcetable,1),INDEX(sourcetable,MATCH(x,sourcetable,1)+1))

Combining the two methods should be as simple as nesting them inside another IF function: IF(x<=365,lookupalgorithm,interpolatealgorithm)