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.

method 1 How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel step 1
Image Source: thewindowsclub

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.

option window
Image Source: thewindowsclub

3 ) Click on the ‘Add-Ins’ Option

In addition t he above step, select the ‘Add-Ins’ option to proceed further.

add-ins window display
Image Source: thewindowsclub

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.

manage window
Image Source: thewindowsclub

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.

analysis toolpak add-ins window
Image Source: thewindowsclub

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.

Image Source: thewindowsclub

6 ) Select the ‘Data Analysis’ Option

Now, select the ‘Data Analysis’ option under the ‘Data’ on the window.

data analysis window
Image Source: thewindowsclub

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.

moving average window
Image Source: thewindowsclub

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.

moving average input output options
Image Source: thewindowsclub

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.

result display
Image Source: thewindowsclub

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.

method 2 How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel step 1
Image Source: thewindowsclub

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.

cursor dragging window
Image Source: thewindowsclub

Now, you will see the results together as below.

Image Source: thewindowsclub

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.

method 3 How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel step 1
Image Source: thewindowsclub

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.

dragging cursor window
Image Source: thewindowsclub

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)

method 4 How to Calculate Simple, Weighted, and Exponential Moving Averages in Excel step 1
Image Source: thewindowsclub

2 ) Put the Formula in the Box

Moving ahead, put the formula ‘=E6$G$1+F5(1-$G$1)’.

formula inserting window
Image Source: thewindowsclub

Then, drag the cursor as above to get the results.

Image Source: thewindowsclub

In this way, following the above-mentioned methods, you can calculate simple, weighted, and exponential moving averages in Excel.