Moving Average

 

This example teaches you how to calculate the moving average of a time series in Excel. A moving average is used to smooth out irregularities (peaks and valleys) to easily recognize trends.

1. First, let's take a look at our time series.

Time Series in Excel

2. On the Data tab, in the Analysis group, click Data Analysis.

Click Data Analysis

Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

3. Select Moving Average and click OK.

Select Moving Average

4. Click in the Input Range box and select the range B2:M2.

5. Click in the Interval box and type 6.

6. Click in the Output Range box and select cell B3.

7. Click OK.

Moving Average Parameters

8. Plot a graph of these values.

Increasing Trend

Explanation: because we set the interval to 6, the moving average is the average of the previous 5 data points and the current data point. As a result, peaks and valleys are smoothed out. The graph shows an increasing trend. Excel cannot calculate the moving average for the first 5 data points because there are not enough previous data points.

9. Repeat steps 2 to 8 for interval = 2 and interval = 4.

Different Intervals

Conclusion: The larger the interval, the more the peaks and valleys are smoothed out. The smaller the interval, the closer the moving averages are to the actual data points.

Do you like this free website? Please follow us on Google+