Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 11 Times Series 441

The average of the values is 5, y 1 is 6, y 2 is 4, y 3 is 8, and so forth, through

yn, which is equal to 7. To fi nd the lag 1 autocorrelation, use the formula for r 1

so that

r 15

1425216252118252142521 c 11725210252

16252211425221 c 1172522

5

214

40

52 0.35

In the same way, the value for r 2 , the lag 2 ACF value, is

r 25

1825216252115252142521 c 11725215252

16252211425221 c 1172522

5

212

40

52 0.30

The values for r 1 and r 2 imply a negative correlation between the current

observation and its lag 1 and lag 2 values (that is, the previous two val-

ues). So a low value at one time point indicates high values for the next two

time points. Now that you’ve seen how to compute r 1 and r 2 , you should be

able to compute r 3 , the lag 3 autocorrelation. Your answer should be 0.275,

a positive correlation, indicating that values of this series are positively

correlated with observations three time points earlier.

Recall from earlier chapters that a constant variance is needed for statisti-

cal inference in simple regression and also for correlation. The same holds

true for the autocorrelation function. The ACF can be misleading for a series

with unstable variance, so it might fi rst be necessary to transform for a con-

stant variance before using the ACF.

Applying the ACF to Annual Mean Temperature

Now apply the ACF to the temperature data. You can use StatPlus to com-

pute and plot the autocorrelation values for you.

To compute the autocorrelation function for the annual mean

temperatures:

1 Click the Temperature sheet tab.

2 Click Time Series from the StatPlus menu and then click ACF Plot.

3 Click the Data Values button and select Fahrenheit from the range

names list. Click OK.

4 Enter 20 in the Calculate ACF up through lag spin box to calculate

the autocorrelations between the mean annual temperature values

and mean temperatures up to 20 years earlier.