Excel trendline accuracy

Solution 1:

It looks like the formula that Excel gave has rounded coefficients. Using an OpenOffice calc routine for regression, I get this formula, which has a much better fit of the data:

y=-0.00017257x³+0.034107417x²-1.89794239x+128.7325785

Since the x³ term is so large, a small difference in the coefficient has a large influence on the predicted result.

Solution 2:

As W_Whalley has discussed this is because Excel is rounding the values displayed in the formula, the fix is to simply change the display formatting for the label, and here's how:

1. Create the graph, add the trendline, make the equation label visible.

2. Right-click the equation label and select Format Data Labels...

3. On the Number tab select the type Number and enter the number of decimal places you want.

4. Close the Format window.

Here's the result of setting the number of decimals places to 20 (for example) for your given example data, with a line break added to avoid scroll bars:

y = -0.00017256831201215700x³ + 0.03410741673273060000x²
                  - 1.89794238802443000000x + 128.73257845634200000000

Solution 3:

If you are not able to derive the regression coefficients by yourself, you can simply substitute r values with r values divided by 10. That is: 30 becomes 3, 60 becomes 6, and so on. You will realize that Excel re-calculate coefficients more precisely, because it will use more significant digits.