Finding a rolling average in Excel
Calculating a rolling average (also known as a moving average) is easy in Excel using the
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
- 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
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:
There are a couple things to note in the formula above:
First, we start entering our formula in cell
Second, we use a relative cell reference to calculate the
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
Excel will automatically fill in formulas to calculate the rolling average per our specifications. Note that the program returns
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!
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
- How to create beautiful table formatting instantly...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...