O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

6.3. Computing Moving Averages

Problem

You'd like to smooth a set of time series data by computing moving averages.

Solution

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.

Discussion

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.

Moving Average trendline

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.

Add Trendline dialog box

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required