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.
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:
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.
Thanks useful for me
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
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.
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.
Post a Comment