Moving Average in Excel

Moving average means we calculate the average of the averages of the data set we have, in excel we have an inbuilt feature for the calculation of moving average which is available in the data analysis tab in the analysis section, it takes an input range and output range with intervals as an output, calculations based on mere formulas in excel to calculate moving average is hard but we have an inbuilt function in excel to do so.

What is Moving Average in Excel

Moving average is a widely used technique in time series analysis that is used to predict the future. The moving averages in a time series are basically constructed by taking averages of various sequential values of another time-series data.

There are three types of moving averages, namely simple moving average, weighted moving average, and exponential moving average in excel.

#1 – Simple moving average in Excel

A simple moving average helps in calculating the average of the last number of periods of a data series. Suppose prices of n period are given. Then the simple moving average is given as

Simple moving average= [P1+P2+………….+Pn]/n

#2 – Weighted moving average in Excel

The weighted moving average provides the weighted average of the last n periods. The weighting decreases with each data point of the previous time period.

Weighted moving average = (Price * weighting factor) + (Price of previous period * weighting factor-1)

#3 – Exponential moving average in Excel

It is similar to a simple moving average that measures trends over a period of time. While simple moving average calculates an average of given data, exponential moving average attaches more weight to the current data.

Exponential moving average =(K x (C – P)) + P

Where,

  • K = exponential smoothing constant
  • C= current price
  • P= previous periods exponential moving average (simple moving average used for first periods calculation)

How to Calculate Moving Average in Excel?

Below are the examples of a moving average in Excel.

You can download this Moving Average Excel Template here – Moving Average Excel Template

Example #1 – Simple Moving Average in Excel

For calculating the simple moving average, we have taken sales data of a company from January to December for the year 2018. Our target is to smooth the data and to know the sales figure in January 2019. We will use three months moving average here.

Moving Average Example 1

  1. The moving average of January, February, and March is calculated by taking the sum of the sales figure of the months and then divided it by 3.


    Moving Average Example 1-1

    Moving Average Example 1-2

  2. Selecting at the corner of the D5 cell and then just dragging and dropping down will give the moving averageMoving AverageMoving Average (MA), commonly used in capital markets, can be defined as a succession of mean that is derived from a successive period of numbers or values and the same would be calculated continually as the new data is available. This can be lagging or trend-following indicator as this would be based on previous numbers.read more for the remaining periods. This is the excel’s fill tool function.


    Moving Average Example 1-3

    The sales prediction for January 2019 is 10456.66667.


    Moving Average Example 1-4

  3. Now, we plot the sales figure and moving average in the line graph to understand the difference in trend. This can be done from the insert tab. Firstly we have selected the data series, and then from the Charts section under insert, we have used the line graph.


    Moving Average Example 1-5

    Moving Average Example 1-6

    After creating the graphs, it can be seen that the graph with the moving average is much more smoothed out than the original data series.

    Moving Average Example 1-7

Example #2 – Simple Moving Average through Data Analysis Tab in Excel

  • Under the Data tab under the Analysis group, we have to click Data Analysis. The following is the screenshot.
Moving Average Example 2
  • From the data analysis, the moving average can be accessed.
Moving Average Example 2-1
  • After clicking the moving average, we have selected the sales figure as the input range.
Moving Average Example 2-2
  • The labels in the first row are clicked in order to make excel understand that the first row has the label name.
Moving Average Example 2-3
  • Interval 3 is selected because we want three years moving average.
Moving Average Example 2-4
  • We have selected the output range with adjacency to the sales figure.
Moving Average Example 2-5
  • We also want to see the chart output, wherein we will be able to see the difference between the actual and forecasted.
Moving Average Example 2-6

This chart shows the difference between actual and Forecasted Moving Average.

Moving Average Example 2-7

Example #3 – Weighted Moving Average in Excel

Moving Average Example 3

We use the three years weighted moving average, and the formula is given in the screenshot.

Moving Average Example 3-1

After using the formula, we got the moving average for a period.

Moving Average Example 3-2

We got the moving average for all other periods by dragging and dropping values in the following cells.

Example 3-3

The forecast for January 2019, i.e., 10718.33

Example 3-4

Now we took the line graph to see the smoothening of data. For this, we have selected our month the forecasted data and then inserted a line graph.

Example 3-5

Now we will compare our forecasted data with our actual data. In the below screenshots, we can easily see the difference between the actual data and forecasted data. The graph on the top is the actual data, and the graph below is the moving average and forecasted data. We can see that the moving average graph has smoothened significantly as compared to the graph that contains the actual data.

Example 3-6
Example 3-7

Example #4 – Exponential Moving Average in Excel

The formula for the exponential moving average is St=α.Yt-1+(1- α)St-1……(1)

Where,

  • Yt-1 = actual observation in the t-1th period
  • St-1= simple moving average in the t-1th period
  • α = smoothening factor, and it varies between .1 and .3. The greater the value of α closer is the chart to the actual values, and the lessen the value of the α, the more smooth the chart will be.

First, we calculate the simple moving average, as shown earlier. After that, we apply the formula given in equation (1). For fixing the α value for all the following values, we have pressed F4.

M A Example 4
M A Example 4-1

We get the values by dragging and dropping in the following cells.

M A Example 4-2

Now, we want to see the comparison between the actual values, simple moving average, and exponential moving average in excel. We have done this by doing a line chart.

M A Example 4-3

From the above screenshot, we can see the difference between the actual sales figure, simple moving average, and exponential moving average in excel.

Things to Remember About Moving Average in Excel

  1. The simple moving average can be calculated using an AVERAGE function in excel
  2. Moving average helps in smoothening the data
  3. Seasonal averages are often termed as a seasonal index
  4. The exponential moving average in excel gives more weight to the recent data than the simple moving average. Therefore smoothening in the case of the exponential moving average in excel is more than that of the simple moving average.
  5. In businesses like a stock market, moving average helps the trader to more easily identify the trend.

Recommended Articles

This has been a guide to Moving Average in Excel. Here we discuss how to calculate 3 types of moving averages in excel (Simple, Weighted, and Exponential) along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Comments

  1. Andrew Alexander says

    THIS EXPLANATIONS ARE SIMPLY EXCELLENT
    THANK YOU

    • Dheeraj Vaidya says

      Thanks for your kind words!

Leave a Reply

Your email address will not be published. Required fields are marked *