How to Calculate Running Average in Excel

Table of Contents

Introduction

Knowing how to calculate running average in Excel can save you a lot of time and energy.

Sample Data

Imagine your Microsoft Excel data consists of “Days of Accidents”. For each day on this list there has been one accident:

As you can see, not all days are listed. There have been no accidents on the days that are not on this list.
If a day is listed more than once it’s because more than one accident happened on that day, each represented by a separate row.

PROBLEM

Now your manager asks you to draw a chart with the following characteristics:

The chart should display the data by WEEKS and not by days. Thus you need to aggregate the daily data into weeks.
She needs you to report the TOTAL NUMBER OF ACCIDENTS for each week.
She also wants you to report the RUNNING AVERAGE for each week defined as the CUMULATIVE TOTAL OF NUMBER OF ACCIDENTS up to and including that specific week, divided by, the CUMULATIVE TOTAL OF NUMBER OF WEEKS up to and including that specific week.

SOLUTION

STEP 1 – First we need to get rid of the “holes” in our list of days.

We need to select and then delete all the blank cells.

Select Column A.
Click F5 to display the GO TO dialog box.
Click SPECIAL to display the GO TO SPECIAL dialog box.
Select BLANKS radio button.
Click OK to select all the blank cells in the column.
Right click on any of the selected blank cells to display the DELETE dialog box.
Select ENTIRE ROW radio button:
Click OK to delete all the blank cells (rows).

STEP 2 – We need to count the number of accidents for each day.

Enter the following formula into Cell B2:
=COUNTIF($A$2:$A$28,A2)
This will give you the total count for the dates. Repeat the procedure for all the other cells to get the following:

STEP 3 – Removing the duplicate cells.

To remove the repeating DATE cells you first need to remove the formulas from Column B since you’d like to remain the date COUNT the same and not revert to “1” when only one of each date group is left in the worksheet.

Copy all the cells and paste them in another location by selecting HOME > Paste > Values & Number. The result looks exactly the same but this time the COUNT column has no formula in it:
Select all the newly pasted columns.
On the DATA tab click the REMOVE DUPLICATES button to display the REMOVE DUPLICATES dialog box:
Click OK to get the CONFIRMATION message:
Click OK to close the message box.

STEP 4 – Aggregating Data into Weeks

To perform this step, we first need to create a PIVOT TABLE.
Select all the cells. On the INSERT TAB click the PIVOT TABLE button to display the CREATE PIVOT TABLE dialog box:

After selecting either the “New Worksheet” or “Existing Worksheet” option to place your table at, click OK. If you select the same worksheet, select a cell for the pivot table’s upper left corner.
At the PIVOT TABLE FIELD LIST pane. First check the COUNT check-box to populate the VALUES quadrant, then the other check-box to populate the ROWS quadrant:

Your PIVOT TABLE is created:

Now, select any date cell, right-click on it, and from the pop-up menu select GROUP to display the GROUPING dialog box:

Select DAYS as grouping criterion and select 7 for Number of Days and click OK. Now your data is aggregated by WEEKS:

STEP 5 – Calculate the RUNNING AVERAGE in Excel.

To calculate running averages, add cell values cumulatively from top down and divide them by the number of weeks corresponding to that cell.
For example, the second running average value is calculated as (6 + 3)/2 and so forth.
Here is the result:

NOTE! Before the fun begins, we need to CONVERT pivot table to a SIMPLE TABLE. Select the pivot table and paste it somewhere else on the same worksheet. The result looks the same but will yield a correct chart with the RUNNING AVERAGES values. Otherwise you will not be able to generate bars for the RUNNING AVERAGES.

STEP 6 – Generate the chart

Now begins the FUN PART!
Select the whole (newly pasted) simple table (NOT the previous pivot table). Then from the INSERT TAB select 2-D CLUSTERED COLUMN CHART:

This chart is good but we want it to display the RUNNING AVERAGES as a TREND LINE. So we need to change the ORANGE frequency bars into a trend line.

Select any of the orange bars and right click to display a pop-up menu.
From the menu select CHANGE SERIES CHART TYPE to display the CHANGE CHART TYPE dialog box.
At the bottom pane, for the RUNNING AVE field, select LINE type from the drop-down menu, then click OK.

Here is the final CHART:

By playing around with various DESIGN options (like CHART STYLES and QUICK LAYOUT options) you can change the appearance of your chart and add additional information. Feel free to experiment with different options. Here is one such improved chart: