Polynomoal Regression

[Guest]

Is there a way to automatically insert the coefficients of a second order
polynomial regression based on three or four data points into prescribed
cells or must they be copied from the equation derived by adding a trendline
to an XY-chart? Thanks.

 

[Peter T]

=LINEST(yVals, xVals^{1,2})

array enter into a row of two cells, or three if you want the final constant

should give you the coefficients a, b & c in =a*x^2 + b*x + c

yVals & xVals being equal sized ranges which could be named

Regards,
Peter T

 

[Guest]

Thanks. Worked like a charm.

=LINEST(yVals, xVals^{1,2})

array enter into a row of two cells, or three if you want the final constant

should give you the coefficients a, b & c in =a*x^2 + b*x + c

yVals & xVals being equal sized ranges which could be named

Regards,
Peter T

 

[Peter T]

Glad it works.

You mentioned copying the equation from a trend line, and in some
circumstances that might be a better approach. There's a long thread here

http://tinyurl.com/a2ysv
Subject: "Extract formula from Text box"
Date: Feb 2005

leading to "total package" solutions to create a 3rd order polynomial
trendline, get the formula, parse it, use it, and return calculated result.
Also some caveats regarding the importance of using precision even if a
precise result is not required, and problems with Linest (in some scenarios
with older versions of Excel).

Regards,
Peter T