If you are looking for a moving average formula in Excel to calculate the moving or rolling average of a dataset then you will find this article useful. Moving average is very much helpful for predicting the sales, demand, stock or temperature, etc. for an upcoming month or day based on taking into account the available values prior to that month or day. So, let’s get into the main article.

## Moving Average Formula in Excel: 8 Examples

Here, we have some records of the demands of a company for the 12 months of the year *2021, and* using these values we will calculate the moving average and forecast the demand for *January* *2022*. We will demonstrate the examples for a 3-point moving average which means that it will require 3 values to calculate this average and the main 3 types of moving averages such as Simple Moving Average, Weighted Moving Average, and Exponential Moving Average will be discussed here.

We have used the *Microsoft Excel 365* version here, you can use any other version according to your convenience.

__Method-1__: Using Arithmetic Operator in Moving Average Formula in Excel

Here, we will use the arithmetic operator **“+” **to calculate the moving average of the demands and forecast the demand for *January 2022*.

** Steps**:

➤ Select a cell in the 3rd row of the dataset as we are using 3 point moving average and as this average requires at least 3 values if we used a cell in the 2nd or first row then it wouldn’t get sufficient values.

➤ Type the following formula in your selected cell

**F6**.

`=(E6+E5+E4)/3`

Here, **E6**, **E5**, and **E4 **are the first 3 demand values to calculate the average by summing them up and then dividing them by **3**.

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

In this way, we are getting the moving averages and in the last cell using this average we can predict the demand for the upcoming *January 2022*.

**Read More: **How to Calculate Running Average in Excel

__Method-2__: Using SUM Function in Moving Average Formula in Excel

Unlike using the arithmetic operator here we will use the **SUM** function to perform the same task as the previous method.

** Steps**:

➤ Use the following formula in the cell

**F6**

`=SUM(E4:E6)/3`

**SUM **will add the values of the cells **E6**, **E5**, and **E4**, then it will be divided by **3**.

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

Afterward, you will get the moving averages for the rest of the cells and the moving average in the last cell will forecast the demand for *January 2022*.

__Method-3__: Using AVERAGE Function in Moving Average Formula in Excel

In this section, we will use the **AVERAGE** function to determine the 3-point average of the demands.

** Steps**:

➤ Use the following formula in the cell

**F6**

`=AVERAGE(E4:E6)`

**AVERAGE **will calculate the average of the values in the cells **E6**, **E5**, and **E4**.

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

Finally, we are getting the averages for all of the remaining cells and we will be able to forecast the demand for the upcoming month of *January* *2022*.

**Read More: **How to Calculate Centered Moving Average in Excel

__Method-4__: Moving Average Formula for Weighted Moving Average

In calculating the **Simple Moving Average **in the previous sections we used the conventional formula for calculating the average, but **Weighted Moving Average **is different in this case. The **Weighted Moving Average **includes some weighted factors which will be multiplied by the values depending on their importance.

As the latest demand value has more significance over the oldest demand values, here we will multiply the latest value with a factor of **0.6 **then the second latest value will be multiplied by **0.4 **and the oldest value will be multiplied by **0.2 **for its less significance in calculating this 3 point moving average.

** Steps**:

➤ Use the following formula in the third row of the

**Moving Average**column.

`=E6*$G$2+E5*$E$2+E4*$C$2`

Here, **E6 **is the latest demand value in *March 2021*, **$G$2 **is the weight factor **0.6 **to multiply with the value in **E6**, then **E5 **is the 2nd latest demand in *February 2021*, **$E$2 **is the weight factor **0.4 **to multiply with the value in **E5 **and finally, **E4 **is the oldest demand in *January 2021*, **$C$2 **is the weight factor **0.2 **to multiply with the value in **E4**.

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

Eventually, you will get the averages for the rest of the cells and the average in the last cell will determine the demand value for the forthcoming month of *January* *2022*.

__Method-5__: Moving Average Formula for Exponential Moving Average

**Exponential Moving Average **is another version of the **Weighted** **Moving Average**, where more weight will be given to the newest data and while going from the latest to the oldest data the average values will decrease exponentially.

** Steps**:

Firstly, we have to calculate the

**Simple Moving Average**values by following

**Method-3**and using the following formula.

`=AVERAGE(E4:E6)`

After getting the **Simple Moving Average **values, we will move to the calculation of the **Exponential Moving Average**.

➤ As we need to have the actual demand value and the **Simple Moving Average **value for the immediately prior time period of the period at which we are calculating the **Exponential Moving Average **we have entered the formula in cell **G7**.

`=0.2*E6+(1-0.2)*F6`

Suppose, we are using this formula to calculate the **Exponential Moving Average **for *April*** 2021 **and so we have used

**E6,**or the actual demand value for

*March***and**

*2021,***F6,**or the

**Simple Moving Average**value for

*March***in the formula.**

*2021*Here, **0.2 **is the** Smoothing factor** which varies from

**0.1-0.3**.

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

Finally, we are getting the averages for the rest of the cell, and using the value in the last cell we will forecast the demand value for *January 2022*.

**Read More:** How to Determine Triple Exponential Moving Average in Excel

__Method-6__: Moving Average Formula in Excel for Last 3 Months Average

Every time you enter the latest **3** months’ demand values you will get the average of these values automatically by using a formula in this method. To do this we will use the **AVERAGE **function, **OFFSET **function, and **COUNT **function.

** Steps**:

➤ Write the following formula in the cell

**F5**

`=AVERAGE(OFFSET(E4,COUNT(E4:E2000)-3,0,3,1))`

**E4 **is the cell from which it will start moving downwards and **E2000 **is the limit of the range until which this formula will work here, so, you can change this number according to your necessary, **COUNT(E4:E2000)-3 **is the number of rows it will move downward, **0 **is the number of columns it moves to the right, the third argument **3 **means the number of rows and the fourth argument **1 **means the number of columns from which the values will be extracted.

**COUNT(E4:E2000) →**counts the cells with values in this range

**Output →**9

**COUNT(E4:E2000)-3 →**becomes

**9-3**

**Output →**6

**OFFSET(E4,COUNT(E4:E2000)-3,0,3,1) →**becomes

**OFFSET(E4,6,0,3,1) →**it will move**6**rows downwards from**E4**, and extract the values from that cell up to**3**rows and**1**column.

**Output →**$E$10:$E$12

**AVERAGE(OFFSET(E4,COUNT(E4:E2000)-3,0,3,1)) →**becomes

**AVERAGE($E$10:$E$12)**

**Output →**418

To check the correctness of this value we will use the following formula in cell **F8**

`=AVERAGE(E10:E12)`

So, we can see that the average of the demands from *July *to *September *is equal for both cases.

Now, we will update our dataset with the demand values of *October*, *November,* and *December *and then we can see that the average value in cell **F5 **has also changed to **446.67**.

To check this value, we have to manually change the reference of the following formula in cell **F8**.

`=AVERAGE(E13:E15)`

In this case, also, the two average values are the same as each other.

__Method-7__: Simple Moving Average for Incomplete Data Using OFFSET and AVERAGE

In the previous cases, we could see that we had to start calculating the moving average from the 3rd row and the cause of the elimination of the 1st two rows was insufficient data. As we know the 3-point moving average requires at least 3 values but the first row only had 1 value and the second row had 2 values.

To solve this problem we will use a formula with the **AVERAGE **function, **OFFSET **function, **MIN **function, and **ROW **function.

** Steps**:

➤ Use the following formula in the cell

**F4**

`=AVERAGE(OFFSET(E4,0,0,-(MIN(ROW()-ROW($E$4)+1,3)),1))`

**ROW() →**gives the current row number

**Output**→ 4

**ROW($E$4) →**returns the row number of this cell

**Output**→ 4

**ROW()-ROW($E$4)+1 →**becomes

**4-4+1**

**Output**→ 1

**MIN(ROW()-ROW($E$4)+1,3) →**becomes

**MIN(1,3)**

**Output**→ 1

**OFFSET(E4,0,0,-(MIN(ROW()-ROW($E$4)+1,3)),1) →**becomes

**OFFSET(E4,0,0,-1,1)**

**Output**→ $E$4

**AVERAGE(OFFSET(E4,0,0,-(MIN(ROW()-ROW($E$4)+1,3)),1)) →**becomes

**AVERAGE($E$4)**

**Output**→ 100

➤ Press **ENTER **and drag down the **Fill** **Handle **tool.

In this way, we will get the **Simple Moving Average **values for all of the cells.

__Method-8__: Using a VBA Code to Get Simple Moving Average

Here, we will use a **VBA** code to calculate the **Simple Moving Average **values of the demands.

** Steps**:

➤ Go to

**Developer**Tab >>

**Visual Basic**Option.

Then, the **Visual Basic Editor **will open up.

➤ Go to **Insert **Tab >> **Module **Option.

After that, a **Module** will be created.

➤ Write the following code

```
Sub movingaverage()
Dim rng As Range
Dim i As Integer
Set rng = Range("E4:E6")
For i = 6 To 15
Cells(i, "F").Value = WorksheetFunction.Average(rng)
Set rng = rng.Offset(1, 0)
Next i
End Sub
```

Firstly, we have set the range **“E4:E6” **as **rng**, **FOR Next loop**, will work through rows **6 **to **15 **to calculate the moving averages, and the output values will appear in column **F **in the corresponding rows.

Then a new range with one row downwards from each cell will be set again with **rng **each time for this loop.

➤ Press **F5**.

Then, we will have the following moving averages.

**Read More: **Calculate Moving Average for Dynamic Range

## Comparison Between the Actual Demand Values and Moving Averages

Here, we will show a comparison between the **Actual Demand**, **Simple Moving Average**, **Weighted Moving Average**, and **Exponential Moving Average **by representing the values together obtained from the previous sections and through a graph for pictorial representation.

Let’s plot the values to visualize through a line chart.

So, we can see that the **Weighted Moving Average (WMA) **is quite close to the **Actual Demand, **and the **Simple Moving Average (SMA)** and **exponential Moving Average (EMA) **are near to each other.

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

**Download Workbook**

## Conclusion

In this article, we tried to cover examples of moving average formulas in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

**Moving Average in Excel: Knowledge Hub**

**Calculate 7 Day Moving Average in Excel****Generate Moving Average in Excel Chart****Displaced Moving Average Formula**

**<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel**