How to Calculate Rolling Average

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.

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.