A cumulative average shows variations and progress over time. As an example, a business runs a sales promotion for five weeks, tabulating the total gross sales for each week. A cumulative average shows the average of the sales totals for the first two weeks, the first three weeks, the first four weeks and the first five weeks in turn. Using Excel, you can calculate the cumulative average and create a graph from the results.
Enter the Data
Video of the Day
Step 1
Open Excel to begin working on a new sheet.
Video of the Day
Step 2
Type in the labels for the first three columns. Enter "Week" (without the quotation marks here and throughout) in cell A2, "Gross Sales" in cell B2 and "Cumulative Average" in cell C2.
Step 3
Enter the week numbers (in this example, "1" through "5") in cells A3 through A7.
Step 4
Enter the sales totals for each week in cells B3 through B7.
Step 5
Type the formula "=AVERAGE(B$3:B4)" in cell C4 to calculate the average for the first two weeks and press "Enter." The "$" in "B$3" is important for the next step.
Step 6
Select cell C4 and press "Ctrl-C" to copy the formula. Select cells C5 through C7 and press "Ctrl-V" to paste the formula. The cumulative averages for the first two weeks will appear in cell C4, the cumulative average for the first three weeks will appear in cell C5 and so on.
Note that the "$" in "B$3" in the average formula freezes cell B3 as the first number in your calculation. If you used "B3" instead, when you pasted the formula, it would shift for each row and you'd get the averages of week one and week two, week two and week three and week three and week four instead of a cumulative average.
Create Your Graph
Step 1
Select cells C2 through C7.
Step 2
Select the "Insert" tab.
Step 3
Select "Line" in the Charts area of the Insert ribbon.
Step 4
Select a chart type you wish to use from the resulting drop-down menu.