How to Calculate Rolling Average in Excel

It is easy to create a rolling 6 months average with Excel. But, there are different steps you’d need to follow, and we will do it together.

Rolling 6 months average

But first you need the data for those six months, and this is something like this:

table data set to calculate the moving average

Click on an empty cell (1), and type =AVERAGE($C$3:$C$3) (2), then press enter.

average formula

Click on cell D4 (1), then write =AVERAGE($C$3:$C$4), and press enter.

average function in Excel

Note: Follow this step on the rest using $C$3:$C$5 (April), $C$3:$C$6 (May), $C$3:$C$7 (June), and $C$3:$C$8 (July).

In conclusion, the steps we just followed is the rolling average for the last six months. You can even use the result to create a chart, or as you wish.

 

Rolling 6 months average using INDEX function

Alternatively, you can use the INDEX function to select the range of cells:

  1. Select the cell where you want to display the rolling average.

  2. Enter the formula: =AVERAGE(INDEX(C:C,ROW()-n+1):INDEX(C:C,ROW()))

    Where:

    • C:C is the column that contains your data.
    • n is the number of data points you want to include in the rolling average.

    For example, if you want to calculate a 6-month rolling average, the formula would be: =AVERAGE(INDEX(C:C,ROW()-5+1):INDEX(C:C,ROW()))

  3. Press Enter to calculate the rolling average for the first set of data.

  4. Copy the formula to the remaining cells in the column.

Note that the formulas will adjust automatically as you copy them to the rest of the cells in the column, and the rolling average will update accordingly.

 

How to calculate a Moving Average?

Calculating a moving average is possible in different ways. It is needed for multiple reasons. However, it begins with having a data that looks something like this:

moving average data tables

Click on an empty cell (1), type =(B4+B5+B6)/3 (2), and press enter.

empty cell enter

 

Note: For the productiveness of performing a moving average, it is preferable that you put the first average value a few rows after the beginning, as you can see in the picture above (1).

If you look at the lower right corner (shown in the picture below), you will see a small square. Double-click on that small square.

double click small square

Note: This is for calculating the moving average for three years.

In conclusion, the result would look something like this:

moving average

The moving mean has been calculated in this easy way with Excel.