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:
Click on an empty cell (1), and type =AVERAGE($C$3:$C$3) (2), then press enter.
Click on cell D4 (1), then write =AVERAGE($C$3:$C$4), and press enter.
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:
Select the cell where you want to display the rolling average.
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()))
Press Enter to calculate the rolling average for the first set of data.
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:
Click on an empty cell (1), type =(B4+B5+B6)/3 (2), and press 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.
Note: This is for calculating the moving average for three years.
In conclusion, the result would look something like this:
The moving mean has been calculated in this easy way with Excel.