Problem: You have a report of revenue by customer, sorted in descending order, shown in Fig. 428. Management will often argue that you should concentrate your best team on the 20 percent of the customers who provide 80 percent of your revenue. How can you calculate a running percentage of the total?
Strategy: I hate solutions that require two different formulas, but this is one of them. You will need one formula for cell C2 and a different formula for cells C3 and below.
-
In cell C2, enter a formula of =B2/$B$18. Format the result as a percentage with one decimal place, as shown in Fig. 429.
Advertisement -
Copy C2 to just the next cell, as shown in Fig. 430. If you want, you can drag the Fill handle down one cell to copy the formula. Or, use Ctrl+C and then Ctrl+V.
-
Hit F2 to edit the cell, as shown in Fig. 431.
Advertisement -
As shown in Fig. 432, type a Plus sign and touch cell C2. Hit Enter.
-
Double-click the Fill handle in C3 to copy this formula down to all of the other cells.
Advertisement
Note that you do not want this formula to be added to your total row. As shown in Fig. 433, the dataset 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.
Additional Information: If you absolutely want to produce this total with a single formula, you could use this formula in C2 and copy it down, as shown in Fig. 434: =SUM(B2:B$2)/B$18. 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, so you might prefer the method shown earlier.
Summary: The formula for a running percentage of the total is another common analysis tool. This technique offers two different options for calculating the formula.
Images
References and Additional Resources
If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel . This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.
Other Resources:
Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com .
