Linear Interpolation In Excel

There are many ways to perform linear interpolation (or extrapolation) in Excel.
The examples below assume that you know at least two points (x1,y1) and (x2,y2) and want to solve for y given x based on the equation for a line: y=mx+b, where m is the slope and b is the y-intercept.

Linear Interpolation

1. Using the Point-Slope formula, where the slope is (y2-y1)/(x2-x1):
y=y1+(x-x1)*(y2-y1)/(x2-x1)
=B2+(B5-A2)*(B3-B2)/(A3-A2)


2. Using the SLOPE and INTERCEPT functions to solve for m and b:
y=SLOPE(y's,x's)*x+INTERCEPT(y's,x's)
=SLOPE(B2:B3,A2:A3)*B5+INTERCEPT(B2:B3,A2:A3)


3. The TREND function uses linear regression to solve for y if you have two or more points. This is also a way to estimate y from a linear fit of many points.
y=TREND(y's,x's,x,TRUE)
=TREND(B2:B3,A2:A3,B5,TRUE)

3 comments:

Anonymous said...

Thank you for the different options. I had already made a custom function from the standard equation. But I like the simplicity of using the existing Trend function.

Anonymous said...

Thanks useful for me

Anonymous said...

I have tried use Trend formula, but why it can't be given accurate value? AS example :
Data A : 1 2 3 4 5
Data B : 12 14 16 20 25

I want to obtain B's value by data A is 3 (just want to testing). The result isn't 14.
May you could help.

Thanks