Linear interpolation with vlookup

kingconsto

New Member
Joined
Apr 19, 2017
Messages
32
Hello,

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

1W0.24
1M0.26
2M0.27
3M0.31
6M0.32
9M0.34
1Y0.35
2Y0.41
3Y0.48
4Y0.56
5Y0.64
10Y1.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 .
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" span="2"> <tbody> </tbody>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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)
 
Upvote 0
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).
 
Upvote 0
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))
 
Upvote 0
to modify Steve's suggestion above slightly to cover the input incl "W", "M" & "Y"


Excel 2012
AB
11W0.24
21M0.26
32M0.27
43M0.31
56M0.32
69M0.34
71Y0.35
82Y0.41
93Y0.48
104Y0.56
115Y0.64
1210Y1.02
13
143.5Y0.512495
154.75M0.324314
Sheet1
Cell Formulas
RangeFormula
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))
 
Upvote 0
FORECAST does linear regression, not interpolation.

A​
B​
C​
D​
E​
F​
1​
Month
Value
Month
Value
2​
0.23​
0.24​
3.5​
0.312​
E2: =PERCENTILE($B$2:$B$13, PERCENTRANK($A$2:$A$13, D2, 6))
3​
1​
0.26​
42​
0.520​
4​
2​
0.27​
5​
3​
0.31​
3.5​
0.288​
E5: =FORECAST(D5, $B$2:$B$13,$A$2:$A$13)
6​
6​
0.32​
42​
0.529​
7​
9​
0.34​
8​
12​
0.35​
9​
24​
0.41​
10​
36​
0.48​
11​
48​
0.56​
12​
60​
0.64​
13​
120​
1.02​
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,487
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top