You are currently offline, waiting for your internet to reconnect

Chart trendline formula is inaccurate in Excel

This article was previously published under Q211967
The equation displayed for a trendline on an xy (scatter) chartis incorrect. When you manually substitute values for the x variable,Microsoft Excel then plots the trendline incorrectly.

Note The trendline formula should only be used when your chart is an XY Scatter chart. This chart plots both the X axis and the Y axis as values. Line, Column, and Bar charts plot only the Y axis as values. The X axis is plotted only as a linear series in these chart types, regardless of what the labels actually are. Therefore, the trendline will be inaccurate if displayed on these types of charts. This behavior is by design.
Microsoft Excel plots trendlines correctly. However, theequation that is displayed may give incorrect results when you manuallytype x values. For appearance, each x value is rounded in the number ofsignificant digits that are displayed in the chart. This behavior allows the equation to occupy less space in the chart area. However, the accuracy of the chart is significantly reduced, which can cause a trend to appear to be incorrect.
To work around this behavior, increase the digits in the trendline equationby increasing the number of decimal places that are displayed. To do this,follow these steps:
  1. In the chart, select the trendline equation.
  2. On the Format menu, click Selected Data Labels.
  3. Click the Number tab, and then click Number in the Category list.
  4. In the Decimal places box, increase the number of decimal places to 30 so that you can see all the decimal places.
  5. Click OK.
match different XL97 XL2000 XL2002 XL2003 XL98 XL2001 XLX XL2004

Article ID: 211967 - Last Review: 09/18/2011 23:31:00 - Revision: 6.0

Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 2004 for Mac, Microsoft Excel X for Mac, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh, Microsoft Excel 2010, Microsoft Office Excel 2007

  • kbchart kbprb KB211967