Linear interpolation with vlookup

[kingconsto]

Hello,

I am trying to get an excel formula that will do linear interpolation for me. The data is set up as follows.

[TABLE="width: 124"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" span="2"> <tbody>[TR]
[TD="class: xl27469, width: 83, bgcolor: white"]1W[/TD]
[TD="class: xl27471, width: 83, bgcolor: white"]0.24[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]1M[/TD]
[TD="class: xl27471, bgcolor: white"]0.26[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]2M[/TD]
[TD="class: xl27471, bgcolor: white"]0.27[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]3M[/TD]
[TD="class: xl27471, bgcolor: white"]0.31[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]6M[/TD]
[TD="class: xl27471, bgcolor: white"]0.32[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]9M[/TD]
[TD="class: xl27471, bgcolor: white"]0.34[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]1Y[/TD]
[TD="class: xl27471, bgcolor: white"]0.35[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]2Y[/TD]
[TD="class: xl27471, bgcolor: white"]0.41[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]3Y[/TD]
[TD="class: xl27471, bgcolor: white"]0.48[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]4Y[/TD]
[TD="class: xl27471, bgcolor: white"]0.56[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]5Y[/TD]
[TD="class: xl27471, bgcolor: white"]0.64[/TD]
[/TR]
[TR]
[TD="class: xl27469, bgcolor: white"]10Y[/TD]
[TD="class: xl27472, bgcolor: white"]1.02 How do I set it up to get the in between times such as what is the answer for 3.5M or 3.5Y .[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If your table is in A1:B12 convert A1:A12 to a single unit placing the results in C1:C12. Lets say years. So C1 is =1/52, C2 is =1/12, C3 is =2/12 etc. Then use a formula like this as an example:

=FORECAST(3.5,B1:B12,C1:C12)

 

[JustynaMK]

Hi kingconsto,

You would need to convert all periods to the same unit, for example:

Rows 1-12
Column A: 1W, 1M, 2M ... 5Y, 10Y
Column B (all converted to "months"): 0.25, 1, 2 ... 60, 120
Column C (values): 0.24, 0.26, 0.27 ... 0.64, 1.02

Then in cell F4 specify the period (in months) for which you would like to see an interpolated value (for example 3.5). In cell G4 paste the following formula:

=INDIRECT(ADDRESS(MATCH($F$4,B:B,1),3))+(INDIRECT(ADDRESS(MATCH($F$4,B:B,1)+1,3))-INDIRECT(ADDRESS(MATCH($F$4,B:B,1),3)))/(INDIRECT(ADDRESS(MATCH($F$4,B:B,1)+1,2))-INDIRECT(ADDRESS(MATCH($F$4,B:B,1),2)))*($F$4-INDIRECT(ADDRESS(MATCH($F$4,B:B,1),2)))

It will return the value of 0.311667 for 3.5 months, or 0.52 for 42 months (3.5Y).

 

[steve the fish]

This should also work for just the data you have:

=FORECAST(3.5,B1:B12,INDEX(LEFT(A1:A12,LEN(A1:A12)-1)/LOOKUP(RIGHT(A1:A12),{"M","W","Y"},{12,52,1}),0))

 

[AlanY]

to modify Steve's suggestion above slightly to cover the input incl "W", "M" & "Y"

Excel 2012
	A	B
1	1W	0.24
2	1M	0.26
3	2M	0.27
4	3M	0.31
5	6M	0.32
6	9M	0.34
7	1Y	0.35
8	2Y	0.41
9	3Y	0.48
10	4Y	0.56
11	5Y	0.64
12	10Y	1.02
13
14	3.5Y	0.512495
15	4.75M	0.324314
Sheet1

Cell Formulas
Range	Formula
B14	=FORECAST(LOOKUP(RIGHT(A14),{"M","W","Y"},{12,1,52})*LEFT(A14,LEN(A14)-1),$B$1:$B$12,INDEX(LEFT($A$1:$A$12,LEN($A$1:$A$12)-1)*LOOKUP(RIGHT($A$1:$A$12),{"M","W","Y"},{12,1,52}),0))

 

[kingconsto]

If your table is in A1:B12 convert A1:A12 to a single unit placing the results in C1:C12. Lets say years. So C1 is =1/52, C2 is =1/12, C3 is =2/12 etc. Then use a formula like this as an example:

=FORECAST(3.5,B1:B12,C1:C12)

Thank you!

 

[shg]

FORECAST does linear regression, not interpolation.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Value​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Value​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.23​

[/td][td]

0.24​

[/td][td][/td][td]

3.5​

[/td][td="bgcolor:#E5E5E5"]

0.312​

[/td][td]E2: =PERCENTILE($B$2:$B$13, PERCENTRANK($A$2:$A$13, D2, 6))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

0.26​

[/td][td][/td][td]

42​

[/td][td="bgcolor:#E5E5E5"]

0.520​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td]

0.27​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td]

0.31​

[/td][td][/td][td]

3.5​

[/td][td="bgcolor:#FFFF00"]

0.288​

[/td][td]E5: =FORECAST(D5, $B$2:$B$13,$A$2:$A$13)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

6​

[/td][td]

0.32​

[/td][td][/td][td]

42​

[/td][td="bgcolor:#FFFF00"]

0.529​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

9​

[/td][td]

0.34​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

12​

[/td][td]

0.35​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

24​

[/td][td]

0.41​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

36​

[/td][td]

0.48​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

48​

[/td][td]

0.56​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

60​

[/td][td]

0.64​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

120​

[/td][td]

1.02​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]