In this tutorial we will learn how to compute Moving Average in Excel. Moving Average can be defined as the Mean of time series data from several consecutive periods. It is called ‘moving’ because it is continually recomputed as new data becomes available, it progresses by dropping the earliest value and adding the latest value.
Data:
The monthly sales data that we use for computing moving average in Excel is shown below
Step 1: First, let’s plot our time series, which is shown below.
Step 2: On the top right corner of the data tab click data analysis.
Note: if you can’t able to find the Data Analysis button? Click here to load the Analysis ToolPak add-in.
Step 3: Select the Moving Average and click ok.
Step 4: select the input range, interval=2 and output range as shown below. Interval value is the interval at which the moving average is calculated.
So the output will be
Step 5: Calculate the moving average for interval =4 and interval=6 as shown in step 4. So the resultant and table and chart will be.
As you can clearly see when the interval for moving average increases the line gets smoother. The smaller the interval, the moving average is more close to the data points which is less smooth.
