You'd like to smooth a set of time series data by computing moving averages.
You can compute moving averages in several ways in Excel. You can use the built-in Moving Average trendline charting feature, the Analysis ToolPak Moving Average feature, or spreadsheet functions to compute moving averages.
Excel allows you to compute moving averages in several ways. I'll show you three ways: using chart trendlines, using the Analysis ToolPak, and using spreadsheet formulas.
As mentioned in Recipe 6.2, you can add a trendline to a chart by selecting the data series and then right-clicking to reveal a pop-up menu. You can then select Add Trendline to open the Add Trendline dialog box shown in Figure 6-4.
Figure 6-4. Add Trendline dialog box
You can select from six different types of trendlines. In this case, select the Moving Average type. To the right of the Moving Average icon, you should see a Period spinner control (as shown in the figure). Use the spinner arrows to select the period over which averages will be computed. You can also type in a number. In this example, I entered 3 so as to create a three-year moving average trendline.
On the Options tab you can type in a name for the new trendline. Figure 6-5 shows the Options tab.
Figure 6-5. Add Trendline: Options tab
Moving Average trendlines have available only ...