Excel bug? Excel calculated Polynomial formula does not match data, why?

Excel calculated Polynomial formula does not match data, why?

This problem has stumped me (no surprise) & stumped my son (surprise, he is finishing his last year as a business major & has good experience with Excel). He also showed it to his professor (not sure what one) and (quote from email) "she was able to replicate the exact same problem we were having, and had no idea what was happening..."

I am using Excel 2003 but the same thing happened under a newer version (my son's Excel 2010, and apparently 2012)

Open the attachment and/or replicate the problem:
Open a new Excel spreadsheet
Post data to cells A2:B5 (A2:A5 as 86, 68, 50, 32 and B2:B5 as 10.5, 35, 108, 276)
Highlight cells A2:B5, select Insert>Chart (or use toolbar Chart button)
Chart Wizard starts
Chart Type: XY (Scatter), click Finish (or Next, Next, Next)
The chart appears in the worksheet.
Right click a data point in the chart & Select "Add Trendline"
TrendlineType - select Polynomial, Order value is 3.
Click on the "Options" tab & check "Display equation on chart" & "Display R-squared value on chart"
Click "OK"
The trendline is added and the equation for the trendline appears:
y = -0.0013x3 + 0.3459x2 - 30.891x + 953.82
or perhaps this is clearer: y = -0.0013X^3 + 0.3459X^2 - 30.891X + 953.82

and the R-squared value is equal to 1 (My understanding is R squared of 1 means the data matches the trendline perfectly with the formula shown)

Now put the formula in the spreadsheet to solve for Y with any value of X
Let cell B8 be the input value for X (I labeled A8 as "Input X:")
Put the formula in B9 as:
= -0.0013*B8^3 + 0.3459*B8^2 - 30.891*B8 + 953.82

When I enter an X value of 32 the calculated Y value is close to the original value of 276 but not exact.
When I enter an X value of 86 the calculated Y value not even close to the expected value of 10.5.

What am I doing wrong? or is this a bug?

Increase the number of decimal places exposed in the trendline label and you will see:

y = -0.00132887517146781x3 + 0.34593621399177500x2 - 30.89094650205780000x + 953.81618655692600000
R² = 0.99999999999998700

If you use this precision in your formula the results are better.

Thank you Izandol, this has solved the mystery & given me the formula I was looking for. The formula does indeed work as expected. (I added to your reputation, thanks again). One more request, could you please tell me how to increase the decimal places in the trendline label? (I tried but can't figure it out)

Originally Posted by Izandol

Increase the number of decimal places exposed in the trendline label and you will see:

y = -0.00132887517146781x3 + 0.34593621399177500x2 - 30.89094650205780000x + 953.81618655692600000
R² = 0.99999999999998700

If you use this precision in your formula the results are better.

Mr Shorty, thank you for pointing me to the LINEST() function. I do not need to do this frequently but, regardless, will (out of curiosity) see if I can learn to use it.

You can select the label and then click the increase decimals button on the formatting toolbar.

Izandol, thank you again. That worked as expected (this time). Before asking for help when I tried the increase decimal button was not available (greyed out) & I thought that was because I had the label selected. Selecting a cell instead brought the increase/decrease decimal buttons back. Thought you must have used another method. Mahalo & aloha!

That is the way how I solved as well.
I chose the equation of the trendline obtained in the graph, and changed its category to number and to 10 decimals places. The resulting line from that equation with more decimals matched the one showed in the graph.