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
Start Free Trial
