XL: Incorrect Polynomial Trendline on XY (Scatter) Chart

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0

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:
  
  
  1. With the right mouse button, click the equation.
  
  
  
  2. Choose the Format Data Labels command from the shortcut menu.
  
  
  
  3. Select Number tab, and select the Scientific category.
     
     The code will be displayed as "0.00E+00."
  
  
  
  4. 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:

trendlines

Additional query words: 5.00c regression

Keywords : kbgraphic
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c; Win95:7.0
Platform : MACINTOSH Win95 WINDOWS
Issue type : kbbug