Read Excel Tips Such As How To Calculate A Moving Average At BrightHub.com

Problem: You have 36 months of sales data, as shown in Fig. 388. In order to create a prediction of sales, you want to calculate a three-month moving average. You will then later create a trendline from the moving average.

See all Microsoft Excel tips

Strategy: You need two months of history before you can begin calculating a three-month moving average.

1) In cell C4, use the formula =AVERAGE(B2:B4).

Note that when you enter this formula, Excel will be concerned because your formula ignores similar data in cell B5. In this case, you are smarter than Excel, so you can use the dropdown on the Exclamation sign to tell Excel to ignore the error, as shown in Fig. 389.

2) Double-click the Fill handle in C4 to copy the formula down to the rest of your dataset.

Result: Moving averages are good if your underlying data has spikes in the sales. It is hard for an automatic system to predict spikes. The moving average smoothes these spikes out of the system, as shown in Fig. 390. A forecast based on the moving average line may be more accurate than a forecast based on the original data.

Summary: Use the AVERAGE function to create a three-month moving average to be used for forecasting.

Functions Discussed: =AVERAGE()

See all Microsoft Excel tips