Excel 2007 Trendline Equations Incorrect

[DanB]

I am using an XY scatter plot. I have applied a polynominal trendline to the
data. The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!
Sometimes it is obvious, the order isn't even correct. Other times the
coefficients are simply incorrect (which is worse). It is present on several
charts, although they are within the same workbook.

There are numerous reports of this on the internet, but I do not see a fix.
I do have SP1 installed. The document was created in Excel 2007, but as a
2003 workbook. The same issue is present on multiple computers with Excel
2007.

The equation however is correct in Excel 2003.

This strikes me as a major bug (assuming it isn't something I am doing
incorrectly) that is likely patched.

Thanks,
Dan

 

[Bernard Liengme]

Under some conditions, Excel 2007 loses the leading coefficient with
polynomial fits. The development team know about this and a hotfix should
appear soon.
Use LINEST to get the values (see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm)
best wishes

 

[DanB]

Interesting. The workaround will work, but isn't much fun for workbooks with
numerous plots of odd data ranges. To display the equation on the chart a
text box has to be manually created.

Thanks for the advice though.

Are the conditions necessary for this published (so that they can be
avoided)? I am shocked such a major bug (displaying an incorrect equation
seems major) has gone unresolved for this long. I imagine complaining isn't
going to help though.

Thanks,
Dan

 

[Ed Ferrero]

Hi Dan,

Bernard has told you how to do what you asked for. Just a word of caution on
using polynomial regression.

I don't know what data you are trying to fit, but I usually discourage
people from using polynomial models unless they have a good reason to
suspect that the 'real life' data should fit a polynomial curve (e.g. a ball
thrown in the air).

If you are trying to fit business data, then polynomial curves are not very
useful IMO. Given a fixed number of data points, you can always find a
polynomial curve with exact fit just by adding enough coefficients. However,
this is not likely to be a good model for the underlying data.

If you have monthly data that may show a seasonal component and a trend,
then it may be better to try smoothing the data with a logarithmic filter
that does not remove too much of the underlying structure. You can download
a Henderson filter from
http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

 

[DanB]

Hi Ed,

I appreciate your advice.

As always, I am skeptical of trend fits. We are fitting a trend to
experimental, which looks quadratic (or cubic), and are using it to
interpolate between points, and not predictions. I did not notice the
equation was incorrect until I took the derivative and it did not match the
plotted results.

I agree that people go nuts with higher order polynomial terms, which can
end up with good R^2 values, but meaningless results.

Using computer results blindly is very dangerous, and I am glad to see that
you are discouraging it.

 

[Steven K. Smith]

What conditions are we talking about? Several of us here use polynomial data
fits with Excel, though for the most part, I calculate them myself.

 

[Rajendra Shah]

Trendline Equations

Dear,

I am used to find the equations for the curves for avoiding tables in programming.

I am happy that the incorrectness of Polynomial equations coefficients is noted.

Particlarly when the coefficients are displayed in scintific notations e.g. 3.0e-10 then it surely incorrect and does not give the correct equation when entered as excel formula.

Further, the polynomial equations are true only for the specified range only and boundry limits must be fixed based on data when using polynomial equations.

I