Table of Contents
To calculate a cumulative average in Excel, you need to create a formula that uses the AVERAGE() function to calculate the average of a range of cells, then use the SUM() function to sum the average of the cells in the range and divide that sum by the total number of cells in the range. This will give you the cumulative average of the range of cells.
A cumulative average tells us the average of a series of values up to a certain point.
The following step-by-step example shows how to calculate a cumulative average for a dataset in Excel.
Step 1: Enter the Data
First, let’s enter the values for a given dataset:
Step 2: Calculate the First Cumulative Average Value
Next, we can use the following formula to calculate the first cumulative average value:
=AVERAGE($A$2:A2)
Step 3: Calculate the Remaining Cumulative Average Values
Next, we can simply copy and paste this formula down to the remaining cells in column B:
We would interpret the cumulative average values as:
- The cumulative average of the first value is 3.
- The cumulative average of the first two values is 4.5.
- The cumulative average of the first three values is 3.
And so on.
Step 4: Plot the Cumulative Average Values
We can plot the cumulative average values by highlighting every value in column B, then clicking the Insert tab along the top ribbon, then clicking Insert Line Chart:
The following tutorials explain how to calculate other common metrics in Excel: