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)

5 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

jmurk said...

I think, that problem is that TREND uses all points given to make a best fitting line out of all points inserted in arguments of TREND function.

To avoid this, you must insert in the function TREND just the nearest two points (upper and lower) to get the accurate result of linear approximation.



Unknown said...

Responding to the latest comment, it appears that the TREND function will calculate a linear equation to fit all the points, and not only the interpolated point between two vertices of the curve. It would be nice to have a function in excel that would automatically find the right rows to interpolate and then use the interpolation formula using these two points as anchors.