Wednesday, July 11, 2012

A Deeper Look at Trendlines

Trendlines in charts are essential when it comes to Viewing the Big Picture. The overall trend of your data is valuable information that may not be readily discernible. Adding a Trendline can make all the difference in the communicative value of your chart.

First of all, let’s review how to add one to a chart. To add a Trendline, simply right-click on the Data Series Line on your chart, and choose Add Trendline from the dropdown list. This will immediately insert a Trendline on your chart.

But Which Trendline Should You Use?

Aye, that is the question! Trendlines come in several different flavors. The key is to choose the one which most reflects the, well, Trend of your data (that may or may not be obvious…). This can be ascertained by determining the R-squared value of the various trendlines. You can easily do this by checking the Add R-squared value to chart box in the Trendline Options area.

To Help you Choose the Best Fit, the Following is a Concise Description of the Various Trendlines:

Linear Trendlines
For simple linear data sets, a linear trendline is a best-fit straight line. Note: Your data is linear if the pattern in its data points approximates a line, and typically shows that something is increasing or decreasing at a steady rate.

Logarithmic Trendlines
A logarithmic trendline that describes a curved line that is used when the rate of change in the data increases or decreases quickly and then levels out.

Exponential Trendlines
An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing rates.

Power Trendlines
Power trendlines are curved lines typically used with data sets that compare measurements that increase at a specific rate. Often used in examples of acceleration.

Polynomial Trendlines
This is a bit of a different animal. A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. Though more challenging to use, it certainly has a place in your Excel Tool Belt.

When you know how to use the easily-mastered Trendlines, a world of quick Excel analysis opens before your eyes. Gotta love it!

No comments: