I cannot display all the poly equation constants on the chart.

[Guest]

Using 2007: when openning up a 2000 spreadsheet, a chart that used to display
the equation for a 6th order poly only displays the the last 3 constants. I
have looked at the trendline formating tools available and every thing is set
correctly. The trendline is drawn correctly and changes, as you would expect,
when I increase the poly order.

 

[Jon Peltier]

I've heard of issues with Excel 2007's display of the polynomial formula.
You could get the values in the worksheet using LINEST, as Bernard explains:

http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Keep in mind that a sixth order fit is generally not physically meaningful,
but may only serve to make the line look nice. Depending on the data you are
plotting, you may find it more sensible to use a relationship that matches a
theoretical model.

- Jon

 

[Guest]

Thanks for your reply Jon,

I was going to add that I'm already using the linest function for the the
number crunching. However, why mess up a good feature. In 2000 I could copy
and paste the contants quickly into a cell to generate a comparison with the
raw data. Will this be fixed or explained?

I have read a few comments about the six order poly should only be used for
making things pretty. I have to disagree. If the number is set to scientific
and 8 sig figs are used, a trendline can replicate raw data points very
accurately. I generate calibration curves for venturi nozzles from NEL
empirical data using excel 2000. The coefficients generated are identical to
other software analysis tools I use for programming.

regards
Daz

 

[Jon Peltier]

The point about 6th order fits is not that Excel's differ from those
calculated by other packages, but that these fits are not based on any
models of physical behavior, but rather on conforming "nicely" to the
measured points. If you are interpolating by using the fit, you will get
reasonable predictions. If you extrapolate too far beyond the range of
observations, you could be very far from the true behavior.

- Jon

 

[Martin Brown]

The point about 6th order fits is not that Excel's differ from those
calculated by other packages,

Although for some difficult problems they do! Historically in old XL
versions the polynomial fit used inside the old graph charting
software had significantly better numerical stability than LINEST. And
I honestly would not trust LINEST with anything beyond a cubic
polynomial in any version of Excel. I have seen it fail too often.

but that these fits are not based on any
models of physical behavior, but rather on conforming "nicely" to the
measured points. If you are interpolating by using the fit, you will get
reasonable predictions. If you extrapolate too far beyond the range of
observations, you could be very far from the true behavior.

It also depends how many measured points you have and their
distribution. If there are too few points then you can get an
excellent least squares fit *at the specified data points* and wild
oscilations inbetween.

It depends very much on what you are doing. Usually it is better to
use a physical model of the problem in hand rather than fitting a
generic high order polynomial. In some circumstance you know on purely
physical grounds that the calibration should for example contain only
odd or even powers of the dependent variable.

Regards,
Martin Brown