Incorrect Polynomial Trendline on XY (Scatter) Chart (114629)
The information in this article applies to:
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for the Macintosh 5.0a
This article was previously published under Q114629 SYMPTOMS
In Microsoft Excel, when you add a third-order polynomial trendline to an
xy (scatter) chart, and you display the equation, both the trendline
and the equation displayed on the chart are incorrect.
CAUSE
This behavior is most noticeable when you are working with an xy (scatter)
chart, and the x values that are fairly close together relative to their
absolute size (that is, the x range is < 20-percent of the mean x value).
The degree to which the polynomial equation is incorrect depends on the
chart data, both the absolute size and size of the range. The greater the
order of the polynomial that you choose for the trendline, the greater the
amount of error in the trendline equation. Also, the greater the chart data
values, the lower the order of the polynomial that displays incorrectly.
Additionally, the amount of error in the trendline equation is greater when
your data sample is smaller than 5 points.
WORKAROUND
Use the following suggestions to avoid this behavior or to at least
minimize the error in the trendline equation when you add a polynomial
trendline to your xy (scatter) chart:
- Use a large number of data points on your chart (that is, more than 5).
- Use the second- or third-order polynomial for the trendline, instead of
a higher order polynomial.
- Use smaller numbers, and a greater range of data points on your chart.
- Use the steps in the following procedure to give the trendline formula a
greater number of significant digits:
- With the right mouse button, click the equation.
- Choose the Format Data Labels command from the shortcut menu.
- Select Number tab, and select the Scientific category.
The code will be displayed as "0.00E+00."
- Add more zeros between the "0.00" and the "E," so that the code
resembles the following example, and then click OK:
0.00000000E+00
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
REFERENCES
For more information about adding a trendline to a data series, choose the
Search button in Help and type:
| Modification Type: | Minor | Last Reviewed: | 8/15/2005 |
|---|
| Keywords: | kbbug kbgraphic KB114629 |
|---|
|