How do I fit a collection of x,y points to a closed form equation, such as a multi-order polynomial, using Excel ?

Jump to: navigation, search

This is borrowed from a mechanical engineering spread sheet. I ran a second-order poly test case and it worked well. Linear Trendline Equation: y = m * x + b m: =SLOPE(y,x) b: =INTERCEPT(y,x) Logarithmic Trendline Equation: y = (c * LN(x)) - b c: =INDEX(LINEST(y,LN(x)),1) b: =INDEX(LINEST(y,LN(x)),1,2) Power Trendline Equation: y=c*x^b c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) b: =INDEX(LINEST(LN(y),LN(x),,),1) Exponential Trendline Equation: y = c *e ^(b * x) c: =EXP(INDEX(LINEST(LN(y),x),1,2)) b: =INDEX(LINEST(LN(y),x),1) 2nd Order Polynomial Trendline Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1) C1: =INDEX(LINEST(y,x^{1,2}),1,2) b = =INDEX(LINEST(y,x^{1,2}),1,3) 3rd Order Polynomial Trendline Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b c3: =INDEX(LINEST(y,x^{1,2,3}),1) c2: =INDEX(LINEST(y,x^{1,2,3}),1,2) C1: =INDEX(LINEST(y,x^{1,2,3}),1,3) b: =INDEX(LINEST(y,x^{1,2,3}),1,4) Higher Order Polynomial Trendline Notice the pattern in the two preceding sets of formulas.

See also

Personal tools