Go Back

Here’s a Guide on Finding the Moving Average in Excel

We can use the AVERAGE function to FIND THE MOVING AVERAGE OF A DATA. Calculation of moving averages is useful for financial forecasting. The steps below will walk through the process.

Figure 1- Using Moving Average for Forecasting Sales for Month 8 and 9

Syntax

=AVERAGE(RANGE)

  • RANGE refers to the cell references that corresponds to the numbers in each cell

Formula

=AVERAGE(B4,C4)

Setting up the Data

With the data in figure 2, we will make a forecast of sales for the next two months. We will use the moving average for every 2 months to calculate the forecast for month 8 and 9.

  • We will enter the Months in Row 3
  • We will enter the Sales in Row 4
  • Our result for moving average for every 2 months will be returned in Row 5

Figure 2 – Setting up the Data

Calculate the MOVING AVERAGE FOR EVERY 2 MONTHS

  • We will click on Cell D5
  • We will insert the formula below into Cell D5
    =AVERAGE(B4,C4)
  • We will press the enter key

Figure 3- Moving Average for the First two months

The average value taken is that of Cell B4 and Cell C4. The next average value will be of Cell C4 and Cell D4 and so on.  

  • We will click on Cell D5 again
  • We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell D5. Select and drag the formula across the row to Cell J5 to copy the formula to the other cells.

Figure 4- Forecast for Month 8 and 9 by Moving Average

If we were to calculate the moving average for every 3 months with the same data, we will use the formula below:

=AVERAGE(B4:D4)

We will click on Cell E5 and insert the formula rather than D5 when working with 2 months sales or 2-month moving average.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar