Extracting Coefficients From A Trend Formula
Can anyone please tell me an easy way to transfer or copy the coefficients of a polynomial equation, say 2nd degree, generated by adding a trend line to an "x-y" scatter chart into cells so they can be used to generate other "y" values using the equation. Thanks.
»
- peteatetk's blog
- Login or register to post comments
- 8886 reads
'=LINEST(known_y's,known_x's,
'=LINEST(known_y's,known_x's,const,stats)
3rd order polynomial
Y = aX2 + bX + c
a coefficient : =INDEX(LINEST(NonLinY,NonLinX^{1,2}),1)
b coefficient : =INDEX(LINEST(NonLinY,NonLinX^{1,2}),1,2)
c coefficient : =INDEX(LINEST(NonLinY,NonLinX^{1,2}),1,3)
where NonLinY are the known Y values
NonLinX are the known X values
All functions must be entered as array functions
Extracting Coefficients
Thanks for your help. I have tried Linest, but it fits a straight line. My problem is with second order polynomial curves. Is there some other technique?
second order polynomial curves
not easy..
take a look at this:
http://excelexperts.com/formula-calculate-maximum-and-minimum-values-x-a...
LINEST for extracting coefficients
Nick:
Your suggested link worked in a round-about-way. It included a link to Chandoo.org that included an example for deriving coefficients from polynomial functions. I stand corrected. LINEST can be used to accomplish our goal. Thank you for the help!