Recently I was asked how many customers made up 25% of my sales. I listed each customer and their sales for a specified period, then sorted descending on sales.
The formula in C2 is copied down for as far as the data goes. The secret is in the referencing.
=SUM($B$2:B2)/SUM($B$2:$B$51)
In the denominator, both B2 and B51 have dollar signs in both positions. They are absolute references and they will not change if the formula is copied to another cell. That’s also true of the first B2 in the numerator. The second B2, however, is relative and will change. By the time the formula is filled down to row 10, it looks like this:
=SUM($B$2:B10)/SUM($B$2:$B$51)
I can use the same formula for all of column C by making some references absolute and some relative.
But this begs the question what best practice would be. Since your denominator SUM range ends at row 51, wouldn’t it be more efficient to sum column B just once, say in B53 with the formula =SUM(B2:B51). Then C2 formula could be =B2/B$53, and the C3 formula could be =C2+C3/B$53, and fill C3 down into C4:C51.
Hi Dick –
Probably very obvious, but a descending sort gives you the fewest number of customers to 25%, an ascending sort would give you the max number, and a company sort would give you a third answer ;-0 to the same question
…mrt
DK,
Love the new “For Normal People” category. Keep it up.
I’m looking forward to the “For Naked People” category.
We do this “80/20? or “ABC” or “Pareto” analysis all the time in warehousing…classifying inventory into basic groups (such as fast=”A”, medium=”B”, slow=”C”) and then determining what percentage of units shipped from the warehouse fall into each group. Typiclaly the top 20% of products (“A” items) account for 80% of units shipped (hence the name “80/20 rule”), and then we store those fastest-moving products in the most accessible locations in the warehouse. It’s uncanny how often you find large sets of data that follow the 80/20 pattern or very close to it. When dealing with thousands of products, we usually create more groups beyond ABC.
I frequently use the approach you describe. If I don’t create summary tables and just need a “Top X Accounts = Y%”, I use:
=”Top “&COUNT($B$2:B2)&” of “&COUNT($B$2:$B$51)&”: “&TEXT(SUM($B$2:B2)/SUM($B$2:$B$51),”0.0%”)
Jason,
I tried using your formula, with Excel 2007, and not having any success. I have values in AI3:AI13. In AJ14 I placed your algorithm, and regardless of how it is modified, an error appears at the “0.00%” position. I’m assuming this is where the value of the Top X% is placed. Whether it stays at 0 or is at 50, an error still appears.
=”Top “&COUNT($AI$3:AI3)&” of “&COUNT($AI$3:$AI$13)&”: “&TEXT(SUM($AI$3:AI3)/SUM($AI$3:$AI$13),”30.0%”)
Why is there the error at “0.00%”
Ron C,
First, your formula has “30.0%”. Change that to “0.0%”. Then insert your formula into AJ3 and copy the formula down to AJ13.
I haven’t tested it in XL2007 but I don’t see any reason why it wouldn’t work.
If you’re still having trouble, save it in a lower version than 2007 and email it to me at:
jasonjmorin AT OPPOSITEOFCOLDmail.com
Jason
Shouldn’t we be able to pivot this? I think Debra has posted some cumulative percent stuff within pivot tables on her site.
Jason,
Could not send it to your email, it bounces back. Mine is Cookr3@uthscsa.edu if you want to send me one and then I can reply. But, I made the changes and it still chokes on “0.0%”
This is what I attempted to put in AJ3.
=”Top “&COUNT($AI$3:AI3)&” of “&COUNT($AI$3:$AI$13)&”: “&TEXT(SUM($AI$3:AI3)/SUM($AI$3:$AI$13),”0.0%”)
Ron
AlexJ, you’re right, Dick could use a Pivot Table % of Column custom calculation.
Ron – Jason’s formula works in Excel 2007. It is basically the same as Dick’s with some explanatory text added.
If you are copying and pasting you will need to edit it to replace all the ” ” characters with proper quote characters (just delete and re-type, or enter the whole formula from the keyboard, rather than pasting).
Doug,
You identified the issue. The double quotes were converted to characters 147 and 148 when I pasted my formula on the website, and Ron C just copied and pasted my formula into his workbook.
Yes, the issue did have to deal with the ” ” marks. After Jason was kind enough to send me his formula, I copied and pasted it into my spreadsheet. It did work. To further verify, I recopied from the website and changed every ” ” mark within the formula, and it went from error to working correctly.
Thanks Jason
This can also be acheived by using the Show Value as “Running Total” option in a pivot table.