[ad_1]
Excel Tip – Calculating A Rolling Or Moving Average Using The OFFSET Function

Calculating moving averages can be a really useful way to look at trends in your data, and we can easily set up a formula in Excel to always look at the last 3 or 6 or any numbers of months in your data.

They are one of the most used and popular indicators. The best place to start is by understanding the most basic type of trend the simple moving average (SMA). No matter how long or short of a moving average you are looking to plot and track, the basic calculations remain the same time and time again.

Let’s take a look at an example. I want to know the last 3 months average sales of my Beanie Hats… always the last three months even when my new monthly sales data is added into my spreadsheet.

I have entered the number of months I want to use for the moving average in cell G6. (In this way I can easily change the number of months I want to look at in my formula- I may at some point want to look at 6 months or 9 months). The formula in G5 ( where I want my formula result to display) reads –

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

Date Volumes

Jun-14 50

Jul-14 65

Aug-14 35

Sep-14 87

Oct-14 99

Nov-14 89

Dec-14 150

Jan-15 250

Feb-15 257

Mar-15 146

Apr-15 150

My Dates re in Column B and my values are in Column C and begin in row 7.

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

First of all, the OFFSET function returns a range in Excel, and we want this to always be the last 3 (or however many specified in our G6 cell). OFFSET takes the following arguments –

reference,rows,cols,height,width

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 amount of cells containing numbers in the column C.. in this case 10. Subtract 3 as we want just the last 3.

Then…

Wrap it all in the AVERAGE function. We now have a moving average calculated automatically by Excel, we can change the number of months easily by changing the value of Cell G6.

[ad_2]

Source by BJ Johnston