XL: Incorrect Polynomial Trendline on XY (Scatter) Chart

Last reviewed: February 2, 1998
Article ID: Q114629
The information in this article applies to:
  • 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: 7.00 5.00 5.00c regression
Keywords : kbgraphic
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.