How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel
Moving average is a very important concept. Further, it is essential for many purposes. In addition, you can use it for the calculation of the average for the data set. So, it gives many beneficial results for you. There are 3 types of moving averages. It includes Simple, Weighted, and Exponential. Moreover, these are useful in their own ways. But, many users don’t know ‘How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel?’. So, we are here for them. We are providing thorough information regarding the same. You can make use of it for your system also. We have also covered the article on ‘How to Insert and Customize a Signature Line in Microsoft Excel‘. Thus, let’s take a ride on ‘How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel?’.
How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel?
Microsoft Excel is an excellent tool for users. Moreover, it has various in-built functions available for the users. So, users can get better results with its application. Thus, we are delivering here a tutorial ‘How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel?’. We assure you that this data will definitely help you in many ways. On the other hand, you should be ready to grab this golden opportunity. So, let’s start this amazing journey.
Methods Used to Calculate Simple, Weighted, and Exponential Moving Averages in Excel
There are different methods available for this purpose. We are providing here some of them for ou.
Method – 1 ) Making Use of Data Analysis Tool Pack
You can make use of the ‘Data Analysis Tool Pack’ in Excel for this operation. It is necessary to follow the steps below to do so.
1 ) Select the ‘File’ Option
You should choose the ‘File’ option as a first step.
2 ) Click on the ‘Options’ Menu
After that, click on the ‘Options’ menu. It is available on the bottom side of the left hand sided list of various options.
3 ) Click on the ‘Add-Ins’ Option
In addition t he above step, select the ‘Add-Ins’ option to proceed further.
4 ) Select the ‘Excel Add-ins’ & ‘Go’ Options
Now, moving ahead, select the ‘Excel Add-ins’ menu from the the box & then click on the ‘Go’ option.
5 ) Choose the ‘Analysis ToolPak’ Option
Further, choose the ‘Analysis ToolPak’ option from the left-hand side segment. Then, click on the ‘OK’ button on the right-hand side of the window. By doing so, this tool will be installed in Excel.
Let’s take an example for calculating this average. This is a chart showing temperature ranges of 10 days. So, you can calculate the simple moving average here.
6 ) Select the ‘Data Analysis’ Option
Now, select the ‘Data Analysis’ option under the ‘Data’ on the window.
7 ) Select the ‘Moving Average’ Option
After that, a pop-up window will appear. So, select the ‘Moving Average’ option from the list. Then, click on the ‘OK’ option on the right-hand side of the window.
8 ) Provide Input & Output Range
In addition to the above step, you should provide the input interval (B2:B11). Then, provide the interval. Moving next, provide the ‘Output Range’ where you want the result. Then, finally, click on the ‘OK’ option on the right-hand side.
9 ) Results Display Window
Here we see the results. ‘#N/A’ for the first 3 rows is because of interval of ‘4’. Then, the other results are displayed.
Thus, you can use this method for the Simple Moving Averages.
Method – 2 ) Calculating the Simple Moving Average Using Formula
You can calculate the Simple Moving Average by using the formula also. It is necessary to follow the steps below to do so.
1 ) Adding the Formula
Let’s suppose, you want to calculate the Simple Moving Average of the first 4 rows in the above example. Then, put the formula in that ‘Day 4’ box & press the enter key. So, the result will display.
2 ) Drag the Cursor
Further, place the mouse cursor on the right-bottom point where you see the ‘+’ sign. Then, hold with the left hand & drag it up to the E11 cell to get all the results.
Now, you will see the results together as below.
Method – 3 ) Calculating the Weighted Moving Average using Formula
There is a formula to calculate the weighted moving average. You should follow the steps given below to do so.
1 ) Put the Formula
You should put the formula as given below putting the values.
Weighted Moving Average = [(Latest value * weight) + (Previous value * weight) + …] / (Sum of all weights)
Here, to calculate 3 point Weighted Moving Average, the weights are assigned as below.
The Latest Value = 70% Weight
The value just before it = 20% Weight
The value before the second one = 10% Weight
So, put the formula ‘=(0.7B4+0.2B3+0.1*B2)/(0.7+0.2+0.1)’ in the E4 box.
2 ) Drag the Cursor
Now, do the same process as above. So, drag the cursor when you see the ‘+’ sign. You will see all the results.
Method – 4 ) Calculating the Exponential Moving Average using Formula
To calculate the Exponential Moving Average, you can use the formula. It is necessary to follow the steps below.
1 ) Use the Basic Formula
You need to make use of SMA & multiplier (Constant=K). Let’s take K=0.3 here.
EMA = Latest Value or Today’s Value * K + Yesterday EMA Value * (1 – K)
2 ) Put the Formula in the Box
Moving ahead, put the formula ‘=E6$G$1+F5(1-$G$1)’.
Then, drag the cursor as above to get the results.
In this way, following the above-mentioned methods, you can calculate simple, weighted, and exponential moving averages in Excel.