What is Moving Average in Excel

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.

Excel has three moving averages: simple moving average, weighted moving average, and exponential moving average.

#1 – Simple moving average in Excel

A simple moving average helps calculate the average of a data series’s last number of periods. For example, suppose prices of n period are given. Then, the simple moving average is shown 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 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 time. However, while a simple moving average calculates an average of given data, an exponential moving average attaches more weight to the current data.

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

Where,

  • K = Exponential smoothing constantC= Current priceP= 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.

Example #1 – Simple Moving Average in Excel

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.

  • From the “Data Analysis,” we can access the “Moving Average.”

  • After clicking the “Moving Average,” we must select the sales figure as the “Input Range.”

  • The “Labels in the first row” is clicked to make Excel understand that the first row has the label name.

  • Interval 3 is selected because we want three years moving average.

  • We have chosen the “Output Range” with adjacency to the sales figure.

  • We also want to see the “Chart Output,” wherein we can see the actual and forecasted differences.

This chart shows the difference between the actual and forecasted moving average.

  • The moving average of January, February, and March is calculated by taking the months’ sales figures and then dividing them by 3. 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. The sales prediction for January 2019 is 10456.66667. 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. 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.

The sales prediction for January 2019 is 10456.66667.

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.

Example #3 – Weighted Moving Average in Excel

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

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

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

The forecast for January 2019 is 10718.33.

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.

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 #4 – Exponential Moving Average in Excel

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

  • Yt-1 = actual observation in the t-1th periodSt-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.

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

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.

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

Things to Remember About Moving Average in Excel

  • The simple moving average can be calculated using an AVERAGE function in Excel.Moving average helps in smoothing the data.Seasonal averages are often termed a seasonal index.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.The moving average helps the trader identify the trend more easily in businesses like the stock market.

This article is a guide to Moving Average in Excel. We discuss how to calculate three 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: –

  • Line Charts in ExcelLine Charts In ExcelLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. read moreRegression vs. ANOVA DifferencesRegression Vs. ANOVA DifferencesBoth the Regression and ANOVA are the statistical models which are used in order to predict the continuous outcome but in case of the regression, continuous outcome is predicted on basis of the one or more than one continuous predictor variables whereas in case of ANOVA continuous outcome is predicted on basis of the one or more than one categorical predictor variables.read moreHow to Create a Pivot Chart in Excel?How To Create A Pivot Chart In Excel?In Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read moreCreate a Dynamic Chart in ExcelCreate A Dynamic Chart In ExcelA dynamic chart is a unique chart in Excel which updates itself when the range of the chart is updated. In static charts, the chart does not change itself when the range is updated.read moreVLOOKUP FalseVLOOKUP FalseIn excel we use the VLOOKUP false function to look for an exact match. In the vlookup function, we can use “1” as the criteria for TRUE and use “0” as the criteria for FALSE. The need for using TRUE may not arise so always stick to FALSE as the criteria for [Range Lookup].read more