Interpolation to find mid numbers

[NG7267]

I have following data set. First column shows tenor in years and second column shows interest rate for the respective tenor.

Tenor
/Years .......... Int rate
10.26............... 6.00%
12.18............... 9.00%
12.35.............. 9.40%
12.52............. 4.50%
12.85............ 13.00%
13.18............ 13.50%

I want to prepare following table from the above data. First column should be middle numbers(half yearly tenors) adding 0.50 years to previous number. Example, to find interpolated interest rate for 11.00 tenor/years, interest rates for closet two tenors from the above table are to be used. For 11.00(tenor/years),its 6% and 9%. Likewise how can I fill the below "? " column? Thank you


Tenor/
Years ................. Interest rate
10.50 ................... ?
11.00 .................. ?
11.50 ................. ?
12.00 ................... ?
12.50 .............. ?
13.00 ............... ?

[zbor]

Here, try this:

[NG7267]

Hi Zbor
Thank you very much for solving the issue. This is perfectly working. Great !!!

[Phuocam]

Here, try this:

If G1 = 10.26 result #DID/0
add conditions like this:
=IF(COUNTIF($A$1:$A$100,G1),VLOOKUP(G1,$A$1:$B$100,2,0),your formula)

[José Augusto]

Hi
Try to use
=FORECAST(G1,OFFSET($A$1:$A$3,1/IFERROR(1/MATCH(G1+STDEV.P(A1:A6)/COUNT(A1:A6),$A$1:$A$6,1),1)-1,1),OFFSET($A$1:$A$3,1/IFERROR(1/MATCH(G1+STDEV.P(A1:A6)/COUNT(A1:A6),$A$1:$A$6,1),1)-1,0))

Please Login or Register  to view this content.

See the differences (@Zbor solution gives me error when I use in G1 the value A1 or A2, ....)
Regards

[JohnTopley]

Given what the OP requested G1 would never be 10.26

10.50 ................... ?
11.00 .................. ?
11.50 ................. ?
12.00 ................... ?
12.50 .............. ?
13.00 ............... ?

[zbor]

I've changed mine solution. This is shorter and it should work as long as you have defined data.

Please Login or Register  to view this content.

Data in A column must be in ascending order.