Moving Average Formula in Excel (8 Uses with Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

moving average formula in Excel

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.

moving average formula in Excel

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.

arithmetic operator

➤ Press ENTER and drag down the Fill Handle tool.

arithmetic operator

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.

moving average formula in Excel

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.

moving average formula in Excel

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.

using the SUM function

➤ Press ENTER and drag down the Fill Handle tool.

using the SUM function

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.

moving average formula in Excel


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.

moving average formula in Excel

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.

using the AVERAGE function

➤ Press ENTER and drag down the Fill Handle tool.

using the AVERAGE function

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.

moving average formula in Excel

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.

moving average formula in Excel

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.

Weighted moving average

➤ Press ENTER and drag down the Fill Handle tool.

Weighted moving average

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.

moving average formula in Excel


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.

moving average formula in Excel

Steps:
Firstly, we have to calculate the Simple Moving Average values by following Method-3 and using the following formula.

=AVERAGE(E4:E6)

Exponential moving average

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 2021, and F6, or the Simple Moving Average value for March 2021 in the formula.

Here, 0.2 is the Smoothing factor which varies from 0.1-0.3.

Exponential moving average

➤ Press ENTER and drag down the Fill Handle tool.

Exponential moving average

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.

moving average formula in Excel

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.

moving average formula in Excel

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

last 3 months average

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.

last 3 months average

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.

moving average formula in Excel

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

=AVERAGE(E13:E15)

last 3 months average

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.

moving average formula in Excel

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

incomplete data

➤ Press ENTER and drag down the Fill Handle tool.

incomplete data

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

moving average formula in Excel


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.

moving average formula in Excel

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

moving average formula in Excel

After that, a Module will be created.

VBA Code

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

VBA Code

➤ Press F5.

Then, we will have the following moving averages.

moving average formula in Excel

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.

comparison

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

comparison

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.

practice


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo