Moving Average In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Moving Average In Excel?

Moving Average in Excel calculates the average of the selected numeric values in subsets in a large dataset to identify a trend or a pattern to predict the future w.r.t data variations. It takes an input range and output range with intervals as an output.

There are 3 types of Excel Moving Average, namely, simple moving average, weighted moving average, and exponential moving average.

For example, we have a data table as shown below.

Select cell D5, enter the formula =(C4+C3+C4)/3, press “Enter”, and drag the formula from D5 to D8 using the fill handle.

Moving Average in Excel - Intro

The output is shown above. Cell D5 returns the moving average for the previous 3 months, and so on for the rest of the dragged cells.

Key Takeaways

  • The Moving Average in Excel helps users find the average of values in 3 different methods, such as simple moving average, weighted moving average, and exponential moving average.
  • The simple moving average can be calculated using an AVERAGE function in Excel.
  • The exponential Moving Average 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.
  • Moving average is a widely used time series analysis technique to predict the future. The moving averages in a time series are constructed by taking averages of various sequential values of another time-series data.

Three types of Excel Moving Average

The three Excel Moving Average types are,

  1. Simple Moving Average.
  2. Weighted Moving Average.
  3. Exponential Moving Average.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#1 – Simple Moving Average

It helps calculate the average of a data series’ last number of periods.

For example, if the prices of n period are given, then the simple moving average formula is,

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

#2 – Weighted Moving Average

It provides the weighted average of the last n periods. The weighting decreases with each data point of the previous period. The formula is

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

#3 – Exponential Moving Average

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

“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?

We can calculate the Excel Moving Average using the built-in feature as follows:

First, select the “Data” tab – go to the “Analysis” group – click the “Data Analysis” option.

The “Data Analysis” window appears. Here, select the “Moving Average” option from the “Analysis Tools” window, and click “OK”.

Download Template

This article must help understand Moving Average in Excel with its formulas and examples. You can download the template here to use it instantly.

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

Examples

We will consider some examples to calculate the Moving Average in Excel.

Example #1 – Simple Moving Average in Excel

To calculate the simple moving average, we have taken a company’s sales data from January to December 2018. Our target is to smooth the data and to know the sales figure in January 2019. Therefore, we will use the three months moving average here.

Moving Average Example 1

  1. The moving average of January, February, and March is calculated by taking the months’ sales figures and then dividing them by 3, i.e., =(C4+C3+C4)/3 in cell D5.


    Moving Average Example 1-1

    Press “Enter”, and we get the output as shown in cell D5.

    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 average for the remaining periods. It is 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. We can do this 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

    The following Line Chart is generated.

    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”. For example, the following is the screenshot.
Moving Average Example 2
  • From the “Data Analysis,” we can access the “Moving Average.”
Moving Average Example 2-1
  • After clicking the “Moving Average,” we must select the sales figure as the “Input Range.”
Moving Average Example 2-2
  • The “Labels in the first row” is clicked 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 chosen the “Output Range” with adjacency to the sales figure.
Moving Average Example 2-5
  • We also want to see the “Chart Output,” wherein we can see the actual and forecasted differences.
Moving Average Example 2-6

This chart shows the difference between the 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 remaining cells, as shown below.

Example 3-3

The forecast for January 2019 is 10718.33.

Example 3-4

Now, we took the Line Graph to see the smoothing of data. For this, we have selected our month and 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 screenshots below, we can easily see the difference between the actual 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 compared to the graph containing 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 them 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

The above screenshot shows the difference between Excel’s actual sales figure, simple moving average, and exponential moving average.

Important Things To Note

  • Ensure to select numeric values to calculate the average in any method, else the cell value with non-numeric value will be ignored.
  • Seasonal averages are often termed a seasonal index.
  • Moving average helps in smoothing the data. The moving average helps the trader identify the trend more easily in businesses like the stock market.

Frequently Asked Questions

1. Where is the Average function to calculate a Simple moving Average?

The Average function is found as follows:
First, choose the numeric values – select the “Formulas” tab – go to the “Function Library” group – click the “More Functions…” option drop-down – click the “Statistical” option right-arrow – select the “Average” function, as shown below.

Moving Average in Excel FAQ 1

2. Why is the Moving Average in Excel not working?

A few reasons the Moving Average in Excel may not work are,
The cell values may have blank, empty cells, or alpha-numeric cells, which are ignored by the formula during the calculation. This may result in incorrect results.
The function name is not rightly entered, which may result in a “#NAME?” error.
The values for the formula are incorrectly referenced.
The cell references for the formula might have been deleted, changed from the dataset.

3. Where is the Line Graph found to plot the Moving Average in Excel?

The Line graph is found as follows:
First, choose the data range – select the “Insert” tab – go to the “Charts” group – click the “Insert Line or Area Chart” option drop-down – select the “Line with Markers” chart type from the “2-D Line” category, as shown below, to generate a Line Chart.

Line graph

This article is a guide to Moving Average in Excel. Here we calculate moving average, Simple, Weighted, Exponential ways, examples, downloadable excel template. You may learn more about Excel from the following articles: –

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 *