Data Analysis with Microsoft Excel: Updated for Office 2007

448 Statistical Methods

Taking the fi ve-year moving average smoothes out the data a bit; however,

even the smoothed values show so much fl uctuation that it is diffi cult to

spot a clear trend (if one exists). We can edit the trendline to increase the

period of the moving average in an attempt to further smooth the data, but

we should use caution because in smoothing the data some crucial informa-

tion could be lost.

EXCEL TIPS

Excel’s Analysis ToolPak also includes a command to calculate

a moving average and display the moving-average values in a

chart. To run the command, open the Data Analysis ToolPak dia-

log box and select Moving Average from the list of analysis tools.

Simple Exponential Smoothing

The moving average gives equal weight to all previous values in the moving

average period. Thus with a fi ve-year period, a value recorded fi ve years ago

is given as much weight as the value from the previous year. Some feel that

•

Figure 11-11
Five year
moving
average of the

change in

mean annual

temperature