Excel trendline accuracy

This is a problem I have every once in a while, and it annoys me tremendously, beacuse I have always to recheck every trendline I get.

An example:

r       L
(mm)
30,00   97,0
60,00   103,2
90,00   106,0
110,00  101,0
125,00  88,0
140,00  62,0
148,00  36,7
152,50  17,0

Upon drawing a trendline (using 3rd order polynomial regression type) with r on the x axis, and L on the y one, Excel will give the formula

y = -0,0002x³ + 0,0341x² - 1,8979x + 128,73

with R² = 0,994. If I interpolate values using that formula for the same values of r as the ones the formula was derived from, I get

r   y
(mm)
30,00   97,083
60,00   94,416
90,00   88,329
110,00  66,371
125,00  33,68
140,00  -17,416
148,00  -53,5912
152,50  -76,97725

which are quite different?

Why does this happen? What is the reason for it?

• @DMA57631 - Edit - That may be, but take into account that those exponents are now so small that even on lower resolutions they are hard to read/distinguish. Besides, I don't think anyone in here misunderstood the original meaning. – Rook Oct 6 '10 at 22:51

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.

• Is it a rounding error in the process, or just in the display of results (the coefficients)? If so, is there a way to increase the number of significant digits, so they suit their purpose better (for like this, they're not only unusable, but unreliable for the future use as well)? – Rook Sep 29 '10 at 14:17
• I don't have Excel to test. I suspect the latter. You may be able to find a way to get more accurate coefficients from Excel. I see Google references to a data analysis add in or an analysis tool pack for Excel. – W_Whalley Sep 29 '10 at 14:44

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
• +1 for telling me about a very important option (number of d.places). Now, W_Whalley did tell the first part of the story, and I won't take +A from him, but just so you know ... I consider this an evenly, or even more useful answer. – Rook Oct 1 '10 at 15:39

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.

• I solved the problem I needed (wrote my own program for it), but yes, I knew of your approach as well. I do't like it though, mostly because I need to rely on regression, not check it up every time. +1 however, just for suggesting (and it is a good suggestion). – Rook Oct 1 '10 at 15:26