Data Analysis with Microsoft Excel: Updated for Office 2007

446 Statistical Methods

For example, if you calculate the average change for each of the last fi ve

years and you do this every year, you are forming a moving average of those

values as you move forward in time. Specifi cally, to calculate the fi ve-year

moving average for values prior to the observation yn, you defi ne the moving

average yma 152 such that

yma 1525

yn 211 yn 221 yn 231 yn 241 yn 25

5

The number of observations used in the moving average is called the

period. Here the period is 5.

Excel provides the ability to add a moving average to a scatterplot using

the Insert Trendline command. Let’s add a fi ve-year moving average to the

change in the temperature for the values from the workbook.

To add a moving average to a chart:

1 Click the Yearly Change chart sheet tab.

2 Right-click the data series (any data value) on the chart to select it

and open the shortcut menu.

3 Click Add Trendline in the shortcut menu.

4 Click the Trendline Options list item if it is not already selected,

click the Moving Average option button, and then click the Period

up spin arrow until 5 appears as the period. Your dialog box should

look like Figure 11-10.