Finding a rolling average in Excel

Calculating a rolling average (also known as a moving average) is easy in Excel using the AVERAGE formula combined with absolute and relative cell references. A rolling average helps smooth out trends over time, particularly when your data shows cyclicality by week, month, or year. Let's take a look at what rolling averages are, and how you can calculate them in Excel.

What is a rolling average?

In many business scenarios, data like product sales may fluctuate significantly due to cyclicality or seasonality. This fluctuation can make it very difficult to tell from looking at raw data whether it is trending in a positive or negative direction. Take, for example, the following scenarios:

• A restaurant wants to figure out whether its number of daily customers is increasing or decreasing. However, the restaurant always receives more customers on the weekends — so comparing the number of customers served on a Tuesday to the number of customers served on the previous Saturday is deceptive. Even though the number of customers served may go down, this might be due to the fact that it's a Tuesday — not due to the fact that the restaurant is actually doing less business.
• A retail manager wants to figure out whether her store is selling more toys each month. She compares December sales to February sales and sees a significant decrease. However, she thinks this data may not be representative of actual sales trends, because the retailer always sells a lot of toys around the holidays.

Both of the problems above can be solved by taking rolling averages — the AVERAGE of several points of data rather than a direct, single-point comparison. Consider the following:

• The restaurant can take the average number of customers served for each day over the past seven days on a rolling basis. So on Saturday, the restaurant will average data from the previous Sunday through Saturday; on Tuesday, the restaurant will average data from the previous Wednesday through Tuesday. This rolling average assures that a full week of data is always accounted for, allowing for accurate comparisons across weeks.
• The retail manager can calculate average monthly sales for the past year on a rolling basis. So in December, she will average data from the previous January through December; in February, she will average data from the previous March through February. This provides her with a more accurate, "rolling" picture of yearly sales as time progresses that controls for seasonality in the sales cycle.

In short, rolling averages are used to smooth out data over a significant period of time and account for seasonality and cyclicality in trends.

Calculating a rolling average in Excel manually

Calculating a rolling average manually in Excel is simple: we create a new column, use the AVERAGE function to calculate an average over a specified period of time, and take advantage of Excel's relative cell reference functionality to ensure that our average moves as our data set progresses.

Let's look at an example using daily sales numbers. Take a look at the following sheet, which shows daily SnackWorld sales of cookies:

As you can see, cookie sales vary widely by day, peaking mid-week when lots of customers are ordering products. Let's use a rolling average to smooth out this trend and figure out what's happening to sales on a longer-term basis:

=AVERAGE(D3:D9)
Output: $5012.37

There are a couple things to note in the formula above:

First, we start entering our formula in cell E9, because September 15th is the first day on which we have a full seven days of data to average. It wouldn't be possible to take a 7-day rolling average starting on September 14, because we have only collected six days of data at that point. Whenever we calculate a rolling average using this method, we'll need to exercise manual judgement to figure out where in our data to start.

Second, we use a relative cell reference to calculate the AVERAGE of the past seven days of data starting on September 15th by averaging the cells D3:D9. The output of this formula is $5012.37. Note that as we copy and paste this formula down into other cells, the range being averaged shifts because we are using relative cell references. For example, the formula copied and pasted to cell E13 will take the average of the range D7:D13, because that represents the trailing 7 days of data:

=AVERAGE(D7:D13)
Output: $5,340.51

With our rolling average calculation, we can clearly tell that weekly sales are trending consistently upwards. This method of calculation has drastically simplified a set of data that was otherwise very difficult to interpret.

Rolling averages using the Analysis ToolPak

We calculate all of our rolling averages manually so that we can exercise full control over the results. But if you'd like for things to be more automatic, you can use a tool called the Analysis ToolPak to speed things up. Here's how it works:

First, open up the Analysis ToolPak by going to Data > Data Analysis:

Next, select the "Moving Average" option from the ToolPak menu:

In the dialogue that follows, enter the input and output cells for your average, as well as your interval. In our example, our input cells are D3:D23, since those cells represent our Sales numbers, and our output cells are E3:E23, since that's where we want our 7-day rolling average to go. In the Interval input box, we put the number 7, because we want to take the rolling average over 7 days. There are a number of additional options in this dialogue you can play around with, including a feature that will automatically generate a chart from our output.

Excel will automatically fill in formulas to calculate the rolling average per our specifications. Note that the program returns #N/A! errors in the cells within which it is impossible to calculate a rolling average with our given 7-day interval.

Now you know everything you need to calculate rolling averages in Excel. Use this handy tool to smooth out your data and control for seasonality and cyclicality when conducting analyses!

Comments