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.
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.
- 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.
- Selecting at the corner of the D5 cell and then just dragging and dropping down will give the moving average for the remaining periods. This is the 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. 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.
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 #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.
- From the data analysis, the moving average can be accessed.
- After clicking the moving average, we have selected the sales figure as the input range.
- The labels in the first row are clicked in order 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 selected the output range with adjacency to the sales figure.
- We also want to see the chart output, wherein we will be able to see the difference between the actual and forecasted.
This chart shows the difference between actual and Forecasted Moving Average.
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, i.e., 10718.33
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.
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 #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.
We get the values by dragging and dropping 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.
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
- The simple moving average can be calculated using an AVERAGE function in excel
- Moving average helps in smoothening the data
- Seasonal averages are often termed as 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.
- 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 –
- Line Charts in Excel
- Regression vs. ANOVA Differences
- How to Create a Pivot Chart in Excel?
- Create a Dynamic Chart in Excel
- VLOOKUP False
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
59.9K subscribers
THIS EXPLANATIONS ARE SIMPLY EXCELLENT
THANK YOU
Thanks for your kind words!