Lesson 36 of 45, Analysis
Running totals & share of total
Quick answer
Anchor one end of a range and let the other end grow as you fill down.
- Key formula
=SUM($B$2:B2)(Running total: locked start, moving end)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
A running total adds everything so far. The trick is a range whose start is locked and whose end moves: =SUM($B$2:B2) in the first row. Fill down and the end becomes B3, B4, B5, so each row adds one more month while the start stays on B2.
A share of total divides each row by the grand total, and the total must be locked on both ends: =B2/SUM($B$2:$B$9). Without the dollar signs, the total range slides down and the percentages are wrong.
Put the two together for a cumulative share: =SUM($B$2:B2)/SUM($B$2:$B$8). On a list sorted largest first, it shows how few products make up 80% of revenue: the 80/20 rule.
Syntax
=SUM($B$2:B2)Running total: locked start, moving end=B2/SUM($B$2:$B$9)Share of the grand total=SUM($B$2:B2)/SUM($B$2:$B$8)Cumulative share (Pareto)=AVERAGE($B$2:B2)Running averageWatch out for
- Lock only the start of a running range: $B$2:B2. If both ends are locked, every row shows the same total.
- A share of total needs both ends locked: $B$2:$B$9.
- Format percentage cells as Percent. Excel shows 0.2 as 20%, so do not multiply by 100.
The exercises
- Running total. In C2, calculate the running total of revenue so far, January through the row's month. Fill down to C9.
- Share of year. In D2, show each month's share of the total for all eight months. Fill down to D9.
- Cumulative share. These products are sorted by revenue, largest first. In C2, show the cumulative share of total revenue up to and including each product. Fill down to C8.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Running total
- Share of year
- Cumulative share