using polynomial trendline equations

[john s]

I am using trendline feature in charting to develop a third order polynomial
equation. The fit is good and I want to use the displayed equation to
calculate nondata point values.
I cannot get the equation to generate points within the original data set.
Is there a conversion factor I need to use or is there a way to output the
trendline equation differently? The trendline coefficients don't seem to
correspond to the data set even though the graph and r-value look good.

 

[Jon Peltier]

1. Use an XY chart, not a line chart.
2. Use as many digits as Excel will provide. Right-click the formula, choose
Format, and on the Number tab, choose a Scientific format with 15 digits
after the decimal.
3. Don't use it for extrapolating. Poly fits go haywire rapidly outside of
the range they were computed over.

Even better than manually copying the trendline formula and parsing out the
coefficients, at least in Excel 2003) is to calculate the coefficients in
the worksheet using LINEST.

- Jon

 

[Tushar Mehta]

You may want to check
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm


On Fri, 25 Jul 2008 11:49:32 -0700, john s

I am using trendline feature in charting to develop a third order
polynomial
equation. The fit is good and I want to use the displayed equation to
calculate nondata point values.
I cannot get the equation to generate points within the original data
set.
Is there a conversion factor I need to use or is there a way to output
the
trendline equation differently? The trendline coefficients don't seem
to
correspond to the data set even though the graph and r-value look
good.

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products