Formula Friday – Calculating A Rolling Or Moving Average Using The OFFSET Function

Written by Barbara ● in Blog,Formula Friday,Formulas

Hello Excellers and welcome to another  #Excel #FormulaFriday blog post in my 2021 series.  Today I want to share how to calculate a rolling or moving average in Excel.  Calculating moving averages can be a really useful way to look at trends in data.   For example, I want to know the last 3 months average sales of my Beanie Hats.  I always want to analyze the last or latest three months, even when my new monthly sales data is added into my spreadsheet.  I, therefore, need the calculations to move along to accommodate the new data that is automatically entered into my data set. The key to this is to use the Excel OFFSET function.

So, it is really easy to set up a formula in Excel to ALWAYS look at the 3 or 6 or any numbers of months in your data. Let’s take a look at an example.  

Calculating A Moving Average.

I have entered the number of months I want to use for the moving average in cell G6 in my example screenshot below.  In this way, I can easily change the number of months I want to look at in my formula.  I might at some point want to look at 6 months or 9 months.  In which case I can simply change this number to the number of months to analyze.

The formula in G5 reads as below:-

=AVERAGE(OFFSET(C7,COUNT(C:C)-G6,0,G6))

Let’s break this down and work out what Excel is doing in this calculation.

147426402
 

Using the OFFSET Function.

First of all, the OFFSET function returns a range in Excel.  We need this to always be the last 3 months (or however many specified in our G6 cell).
 
The OFFSET takes the following arguments-
 
reference – this is the reference from which you want the base offset.  It is a reference to a cell or range of adjacent cells.
 
rows – this is the number of rows wither up or down that you want the upper-left cell of the result to refer to.
 
cols – this refers to the number of columns to the left or to the right that you want the upper-left cell of the result to refer to.
 
height – the number of rows (height) that you want the result to be.  If this value is omitted then it will default to the same height as the reference value.
 
width – as in the height, this is the width in the number of rows that you want the result to be.  If this value is omitted then it will default to the same width as the reference value.
 

Bringing It All Together. Offet and Moving Average.

So, we will tell the OFFSET function to create a new range with the starting cell being 7 cells below C4 (the first volume cell), and continuing for 3 cells down.  How does it know to start 7 cells down?
 
 
By entering COUNT(C:C)-G6 as the reference, COUNT(C:C) returns the number of cells containing numbers in column C.. in this case, 10. Subtract 3 as we want just the last 3. Then… Wrap it all in the AVERAGE function. Ta-da!.
create a rolling or moving average in Excel
So, the average sales volumes for the last three months of data we have is 217. 
Let’s add in another month sales volumes…….for April 2015, and Excel automatically updates our formula.  Sweet.
Excel moving average
Let’s road test looking at 6 months average sales, by changing our reference in G6 to 6 for six months average volumes.  A moving average!.
Excel OFFSET function a moving average
This job is done.  We have created a dynamic formula using the OFFSET function.  This will always give us the last x number of months average sales.  We have created a moving average! Great Stuff!.

So, What Next? Want More Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

How To Excel At Excel – Formula Friday Blog Posts.

More Excel Tips_New1
Learn Excel With Our Excel Online Courses


 

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue,  where you can get help with Excel FAST. Why not check it out?.

ExcelRescue.net

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts

© 2024 Copyright How To Excel At Excel