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 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 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 you.

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 to the 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 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 from Display Window

Here we see the results. ‘#N/A’ for the first 3 rows is because of the 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 it 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 points 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.