Microsoft Office Tutorials and References
In Depth Information
Other Trending Techniques
Figure 8-31: The volatile nature of this data makes it difficult to seek the underlying trend.
You can use lots of different techniques to smooth a dataset. Take a moment to walk through two of
the easier ways to apply smoothing.
Smoothing with Excel’s moving average functionality
Excel has a built-in smoothing mechanism in the form of a moving average trend line. That is, a trend
line that calculates and plots the moving average at each data point. A moving average is a statistical
operation that is used to track daily, weekly, or monthly patterns. A typical moving average starts
calculating the average of a fixed number of data points; then with each new day’s (or week’s or
month’s) numbers, the oldest number is dropped, and the newest number is included in the average.
This calculation is repeated over the entire dataset, creating a trend that represents the average at
specific points in time.
Figure 8-32 illustrates how Excel’s moving average trend line can help smooth volatile data,
highlighting a predictable range.
In this example, a four-month moving average is applied.
Figure 8-32: A four–month moving average trend line is added to smooth the volatile nature
of the original data.