Calculating moving averages can be a really useful way to see trends in your data, and we can easily set up a formula in Excel to always see the last 3 or 6 or any number of months in your data.
They are one of the most used and popular indicators. The best place to start is to understand the most basic type of trend, the Simple Moving Average (SMA). No matter how long or short the moving average you are looking to plot and track, the basic calculations remain the same time after time.
Let’s take a look at an example. I want to know the average sales of my beanie hats for the last 3 months… always the last three months, even when my new monthly sales data is added to my spreadsheet.
I entered the number of months I want to use for the moving average in cell G6. (This way I can easily change the number of months I want to see in my formula – at some point I may want to see 6 months or 9 months.) The formula in G5 (where I want the result of my formula to display) says:
=AVERAGE(OFFSET(C7,COUNTER(C:C)-G6,0,G6))
date volumes
14-June 50
14-Jul 65
Aug-14 35
14-Sep-87
October 14, 99
14-Nov-89
Dec-14 150
Jan-15 250
15-Feb-257
March 15, 146
15-Apr-150
My Dates are in Column B and my Values are in Column C and start at row 7.
Let’s break this down and find out what Excel is doing.
First of all, the OFFSET function returns a range in Excel, and we want it to always be the last 3 (or the amount specified in our cell G6). OFFSET takes the following arguments:
reference,rows,cols,height,width
So, we’ll tell the OFFSET function to create a new range with the starting cell 7 cells below C4 (the first volume cell) and continuing 3 cells down. How do you know to start 7 cells down?
Entering COUNT(C:C)-G6 as a reference, COUNT(C:C) returns the number of cells that contain numbers in column C.. in this case 10. Subtract 3 since we want only the last 3.
After…
Wrap it all up in the AVERAGE function. Now we have a moving average calculated automatically by Excel, we can easily change the number of months by changing the value in cell G6.
