Md. Abdul Kader
Moving average is a common term in time-series analysis to review previous data and predict the future. In this article, I’ll show the fundamental concept of moving average, its types, calculation methods, and especially the process of creating moving average in an Excel chart.
What is Moving Average in Excel?
Theoretically, the definition of moving average may differ a little bit as its usage includes in the field of statistics, finance, marketing, and so on.
Whatever, the tool can be used by investors to analyze the direction of trends and traders to monitor the price.
Basically, there are three types of moving averages.
1. Simple Moving Average
The average of the latest number of periods of a data series can be calculated using a simple moving average.
2. Weighted Moving Average (WMA)
The weighted moving average calculates the average of the previous n periods. Each data point from the prior time period decreases the weighting.
3. Exponential Moving Average (EMA)
While a simple moving average determines the average of a set of data, an exponential moving average gives the current data more weight.
How to Calculate Moving Average in Excel
We can easily calculate the moving average in Excel by using two simple functions.
If you want to utilize the AVERAGE function, the formula will be like the following-
=AVERAGE(C5:C6)
Here, C5 is the precipitation in January, C6 is the precipitation in February.
On the other hand, if you want to use the SUM function, the formula will be-
=SUM(C5:C6)/2
The two calculation methods are given in the following figure. Just have a glimpse of it.
This is the turning point of our article. Now, we’ll see the process of making charts of different types of moving averages in Excel. Before that, let’s be introduced with the dataset. This is the monthly precipitation data of Washington D.C. which is adapted from the US Climate Data.
1. Generating Moving Average in Excel Chart
Do you know the process of creating charts in Excel?
If not, don’t be worried as it is quite simple.
⇒Firstly, select data
⇒Click Insert tabs>Charts ribbon
⇒Click Recommended Charts or any chart option
After that, you’ll see the following figure.
I have chosen the Scatter chart option, but you can pick another one depending on your purpose.
Finally, a chart is created and this is like the following.
Then, follow the steps to create a moving average line.
⇒Click on the Plus (+) icon in the upper-right corner of the chart.
⇒Move the cursor on the Right arrow of the Trendline element.
⇒Choose the Two Period Moving Average option.
Thereafter, you’ll see the following moving average line for a 2-months period.
It is an interesting fact that we can generate a simple moving average line in the chart without the calculation process using a formula.
Read More: How to Calculate Average in Excel (Including All Criteria)
2. Producing Simple Moving Average in Excel Chart
Also, the previous one is an example of a simple moving average, but in this section, you’ll learn an interesting thing: adding a second moving average line.
Before that, let’s see the process of adding a 3-months moving average line.
2.1. Simple Moving Average for 3-months
As you know, you can calculate the moving average for different periods. In the earlier example, you saw the moving average line for 2 months. But you can find the line for different months.
For making a moving average line for 3-months, follow the steps below.
⇒Pick More options from the Trendline element by moving your cursor on the element.
⇒You’ll see a Format Trendline toolbar on the right side of Excel.
⇒Click on Trendline Options.
⇒Choose the Moving Average option.
⇒Fix the period as 3.
Or if you want you may click on the 2-months moving average line, you’ll discover the above Format Trendline toolbar in the same place. Then fix the period as 3.
Ultimately, you’ll see the following output.
2.2. Adding a Second Simple Moving Average Line in Excel Chart
You have to follow a similar process to add a second simple moving average line like the before indeed. Simply choose the period as 5.
Eventually, the output will look like this.
3. Calculating Weighted Moving Average in Excel Chart
Since you are aware that the weightage factor is compulsory in creating the weighted moving average line in the Excel chart.
The factor may be 0.5, 0.3, and 0.2 sequentially, also you can modify this factor.
So the formula will be-
=0.5*C7+0.3*C6+0.2*C5
Here, C7 is the precipitation in March, C6 is the precipitation in February and C5 is for the precipitation in January.
If you select “Month”, “Precipitation in mm”, and “WMA” fields, and then insert line chart. As you are fully conscious that the process of generating a chart has been discussed at the beginning of this article.
Lastly, the output will be as follows.
The above picture clearly reveals that the weightage moving average provides a more smooth moving average line than a simple moving average line.
4. Creating Exponential Moving Average in Excel Chart
Not to mention that is our last example. Though the method of generating exponential moving average line is a little bit complex, it will be easier if you understand some concepts and the following formula.
St=α.Yt-1+(1- α)St-1
Formula Explanation:
Yt-1 means actual observation is made in the t-1th period
St-1 refers to the simple moving average (SMA) in the t-1th period
α (Alpha) denotes the smoothing factor which can be calculated using the formula below.
α=2/(N+1)
Here, N is the value of the period.
I would like to mention that if the value of α is higher, the line will be less smooth.
Now, input the following formula in Excel.
=$H$6*C6+(1-$H$6)*D7
Formula Breakdown:
Here, H6 is the value of α where $ (dollar sign) is used as it is fixed
C6 is is the precipitation of February (actual observation)
D7 is the simple moving average for 3-months.
At this moment, if you select the precipitation, SMA, and EMA fields and insert the line chart, you’ll see the following output.
It is clear that the weighted moving average (WMA) line is smoother than the exponential moving average (EMA) line. If the α is lower, the EMA line will be smoother.
Read More: How to Calculate Exponential Moving Average in Excel
Things to Remember
1. Be careful when selecting the cell for getting output as the period is related to this. For example, if you want to calculate a simple moving average line for 3-months, you have to blank above two cells.
2. Weighted moving average (WMA) and exponential moving average (EMA) can be used for forecasting.
Download Practice Workbook
Conclusion
This is how you may calculate the moving average in an Excel chart. I hope that this article will accelerate your Excel learning. If you have any queries or suggestions, please let me know in the comments section below.
