Wednesday, July 11, 2012
A Deeper Look at Trendlines
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:
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.
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.
An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing rates.
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.
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!