Trendline Fitting Errors
In a recent post at the PTS Blog titled Trendline Fitting Errors, Jon Peltier describes some of the problems that result from fitting data to trendlines created as a part of an Excel chart. Microsoft seems to acknowledge this problem via their Knowledge Base Article 211967, although the article is somewhat confusing.
Refering to the article and under the header "Symptoms", it states "The equation displayed for a trendline on an xy (scatter) chart is incorrect." Several sentences later, the article states "The trendline formula should only be used when your chart is an XY Scatter chart."
Finally, the article does state the reason why trendlines should only be used on XY Scatter charts: "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." I wonder why Microsoft continues to offer trendlines as a component of Line, Column, and Bar charts if they are incorrect?
Below are some useful resources for creating and interpretting trendlines:
- Choosing the Right Trendline for Your Data from Microsoft.
- Trendline Analysis with Excel from Process Trends.
- Trendline Charting Formulas from The Spreadsheet Page.
