Polynomial Trendlines in Excel 2007

[diederick]

I have applied a polynominal trendline to a XY Scatter plot.

The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!


It however appears to be an error on a certain version of Excel 2007, as a few of the users in my office also get the incorrect equation being displayed, but there are users who do get the correct equation.


The problem seems to be around the leading coefficient, when the intercept = 0.0


Any help/advise would be much appreciated!!

 

[Hui]

It is a rounding error in that 4 decimal places doesn't give you enough precision to manually match the curve which has been done at full precision.


To Fix

Select the text box that contains the equation on the chart

Right click and select Format Trendline Label

On the Number tab change the number format to something like:

#,##0.000000000000000

That's 15 trailing 0's


Now try the equation again


For more info on this have a read of: http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/

where I discuss exactly this issue in the Limitations section.

 

[diederick]

Thanks...but unfortunately it's not that!!

As a standard I always use (ironically enough!!) 15 decimals!


What I'm struggeling with is that for exactly the same set of values, there are 2 different answers being displayed on the different computers (all running Excel 2007, but which was loaded from two different packs). I am assuming that a certain version of Excel 2007, might contain an error!


Furthermore the problem occurs only when the intercept = 0.0. An incorrect leading coefficient is displayed...from what I can see, it's consistantly giving the same coefficient irrispective of the intercept = 0.0 or not, whereas the set of computers displaying what seems to be the correct equation, generally have got different leading coefficients when you change between intercept = 0.0 and the alternative.


Can a picture of a graph be put up somewhere on this blog, then one can visually see the difference.

 

[Hui]

Can you post the file or part of it somewhere, refer http://chandoo.org/forums/topic/posting-a-sample-workbook


or email me direct, my email is at the bottom of http://chandoo.org/wp/about-hui/


I'm keen to see what is wrong here?

 

[diederick]

Thanks Hui!


I mailed the file to you.

 

[Hui]

Diederick


Interesting


I opened your file and the Regression parameters in X5:X8 don't match the regression parameters in the equation box

I copied the regression parameters in the eqn box and put them in the yellow cells

When you copy them in to X5:X8 the regression curve fits perfectly


Now I am using Excel 2010 and i suspect that they have made some code improvements between 2003/2007/2010

http://blogs.office.com/b/microsoft-excel/archive/2009/09/10/function-improvements-in-excel-2010.aspx

Although not specifically mentioning Chart trendlines, a lot of the code internally will be common and the improvements may have fixed the problem


So maybe its time to upgrade