Microsoft KB Archive/114629

= Incorrect Polynomial Trendline on XY (Scatter) Chart =

Article ID: 114629

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



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 </li></ol> </li></ul>

<div class="status_section">

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.

<div class="references_section">