Problem: I have a report of revenue by customer, sorted in descending order. Management consultants often argue that it's important to concentrate the best team on the 20% of the customers who provide 80% of the company's revenue. How can I calculate a cumulative running percentage of the total so I can determine which 20% of customers to focus on?
Strategy: I hate solutions that require two different formulas, but the intuitive solution to this problem is one of them. You will need one formula for cell C2 and a different formula for cells C3 and below. Here's what you do:
- In cell C2, enter the formula =B2/$B$18. Format the result as a percentage with one decimal place.
- Copy C2 to just the next cell, either by dragging the fill handle down one cell or using Ctrl+C and then Ctrl+V.
- Press F2 to edit cell C3.
- Type a plus sign and touch cell C2. Press Ctrl+Enter.
- Double-click the fill handle in C3 to copy this formula down to all the other cells. Note that you do not want this formula to be added to your total row. As shown below, the data set was purposely set up with the total row and the data separated by a blank row in order to prevent this formula from copying to the total row.
- Add this row's percentage of the total to the previous row.
Alternate Strategy: If you absolutely want to produce this total with a single formula, you could use the formula =SUM(B2:B$2)/B$18 in C2 and copy it down. This works because the range B2:B$2 is an interesting reference: It says to add up everything from the current row to the top row. This formula seems a bit less intuitive. For large data sets, it will take much longer to calculate than the first method. (See Consider Formula Speed for details.)