In this post, you will learn how to calculate the running average of a data set using Excel’s Average(…) function.
A business generates a huge amount of data every year. Most of the data are related to time. One useful way to analyze your data is to find the average.
For example, assume that you run a store in your town. And you keep a record of your customers:
- How many customers come to your store every month,
- And how many times a customer comes to your store.
Or if you run a website or blog like me, then you will count how many visitors are coming to your blog every month.
These are just two examples; your business or website generates many more kinds of data.
In this article, we shall learn how to calculate the running average of some values.
Calculate Running Average
Let’s explain what the running average is with an example. In the image below, you are seeing an Excel workbook titled Running Average. In the Visitors worksheet, you are seeing some data. Under the Month column, there are twelve months: from Jan to Dec. Under visitors in my blog column, you are seeing the total number of visitors coming to my blog every month. The blog visitors have increased almost three times in the month of November. In Jan it was around 3 thousand and in the month of November, it is around 8 thousands and 500 hundreds.
At first we shall find out the running average of these values and then will find out the 3-month running average of these values. Then we shall talk about the differences between these two running averages.
To follow along with me with this article, first, download the Running_Averages.xlsx workbook.
Okay let’s find out the regular running average at first.
- Select cell C2 and insert the average function, select cell B2 as the argument of the Average function, I am going to make it B2 reference as an absolute reference, so press the F4 key in your keyboard, cell reference B2 turns into an absolute cell reference.
- Then insert range operator and again insert cell reference B2 and finally place the closing parenthesis after the formula.
- Now I press return. The formula returns a value of 2901. This formula actually finds out the average of cell B2.
- Now I select the cell and then copy the formula for the next cell. The formula returns 3022.5. I select the cell and click on the formula bar. You see this formula selects cells B2 to B3. So the formula calculates the average of cells B2 to B3. I am sure you are getting the idea of how the running average actually works.
- Now I copy the formula for other cells in the column. I select the last cell and click on the formula bar. You see this formula works on all the cells in the column, from cells B2 to B13.
So, this is how you can calculate running average of some values.
Now I shall show how you can calculate the 3-month running average.
- Under 3-month running average column, I select the third cell D4. As we are going to find out the running average of 3 months, so we have to start from the third month. You cannot find out 3-month running averages using only 1 or 2 values.
- I insert the average function in cell D4, I select cells B2 to B4. This time I am not going to turn any cell references to absolute as when I shall copy this formula to the cells below, I want that for every formula it will select the last three values. So I close the function and press Enter.
- The formula returns 3202. It is the same as this running average. The reason is this running average finds out the average of these three values and this 3-month running average also does the same.
- Now I copy the formula to other cells in the column. You get all 3-month running average.
Now let’s come to the point. What you say, which running average is showing the more practical scenarios? The regular running average or the 3-month running average? As a blogger what should my expected number of visitors in the month of Jan in year 2016? Is it 5511 or 7970?
Of course I will expect at least 8000 visitors will come to my blog in the month of January 2016, not 5511. 5511 is actually showing the result based on the last 12 months. And 7970 is actually showing the result based on the last 3 months. It is more practical.
So when you will analyze your business data, it is more practical to use the 3-month running average than the regular running average as recent values show more analytic about your business.
You might ask yourself then which average you should use. My answer will be both. If you calculate both types of running averages for your data, you will get two different looks of your data.