Link to home
Start Free TrialLog in
Avatar of sim2k_support
sim2k_supportFlag for United States of America

asked on

Excel Polynomial Trendline Equation doesn't accurately predict future data

A user in our office is analyzing a large set of data with MS Excel.  When graphing the data, the user applies a 6th order polynomial trendline.  The trendline has this equation:

y = -1E-12x6 + 6E-10x5 - 1E-07x4 + 9E-06x3 - 0.0004x2 + 0.0102x - 0.1289
and an r-squared value of:
R2 = 0.9917

With an R-squared value that high, you would expect the equation to accurately predict future values (but again, this is Microsoft).  According to the trendline, when X equals 121, you get an expected Y value around 0.07-0.08 (eyeballing it by looking at the graph).  When we put the function to test, Y returns a value of 2.1811.  

I've tried flipping the signs around, no luck.  I've had other people check my work but they drew the same conclusion.  Have other people run into similar problems where the Excel trendline equation is wrong?  Please feel free to check my work (see if you get 2.1811).  Any recommendations?  I've seen other posts recommending more specific software (MathCAD et al) for advanced calculations.  Obviously, I can't post the data set so it'd be hard for anyone else to use a different program to test.

The user is pretty upset about the discrepancy and is insisting we call Microsoft to report the error, but I don't think that getting into an argument about this with Microsoft will be productive (though probably expensive and time consuming).
thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sim2k_support

ASKER

Thanks for the insight, I hadn't thought about significant figures but yes, that could really make a difference!
Ben,
One might also question whether a 6th order polynomial is really necessary. I've done a lot of engineering and scientific correlations, and only use 6th order polynomials when I'm not insightful enough to find the right type of equation to use.
Brad
sim2k_support,

I think you will find that the 6th order polynomial trend line formula will not of any use at all for predicts results - irrespective of the number of significant figures. I have played around with it for hours and I have found it to be extremelyt unreliable. The trend line on the other hand reflects the data rather well.

Patrick
Oops...

sim2k_support,

I think you will find that the 6th order polynomial trend line formula will not of any use at all for predicting results - irrespective of the number of significant figures. I have played around with it for hours and I have found it to be extremelyt unreliable. The trend line on the other hand reflects the data rather well.

Patrick
Avatar of jsemenak
jsemenak

The formula
y = -1E-12x6 + 6E-10x5 - 1E-07x4 + 9E-06x3 - 0.0004x2 + 0.0102x - 0.1289

is interesting first 4 items only ONE valid figure
if I change -1 to -2 in first irtem result drop from 2,18 to - 0,95

It is nonsence that excel badly calculates, anyhow it has some errors, but no so fatal.  
And practically is nonsence that only one valid figures in some item of result.
I think u copy formula from graph.

DO  recalculate in worksheet by LINEST () or maybe exist direct fuction for polynomial regression.  
If you are getting the equation from a chart, then you can force Excel to display more significant figures:
1) Right-click the equation
2) Format...Data Labels...Number
3) Increase the number of significant figures as desired. I suggest using scientific notation.
Brad - I have always used scientific notation for the interpretation of trend line formulae given by Excel and not so far managed to get a sensible, usable formula out of the 6th order polynomial chart. - Patrick
Thanks to all for their suggestions.  Two things here:
1.  It was an issue with significant figures.
2.  The Excel snap-in XLstat does a pretty good job of solving the trendline with an appropriate number of sig figs right out of the box.