What would Pareto do?

June 15, 2009 7:25 PM

What is the best way to set up data and formulas in Excel 2007 to count the number of products that make up x% of sales volume?

I am trying to determine the number of products that account for 80% of my sales volume. I need a function that can do this for me. My worksheet/pivot table is arranged as follows:


Number of Units Percent of Volume
Total Units 100 100%
Product A 35 35%
Product B 25 25%
Product C 20 20%
Product D 10 10%
Product E 8 8%
Product F 2 2%

I would like to count the number of products that comprise 80% of sales volume, starting with the product that accounts for the greatest volume. In this example, the function would count product A, product B and product C because they total 80%. (In reality there are hundreds of products, so a formula is essential.)

I feel like there is a SUMIF or COUNTIF function that can do this if I add in some conditions, but I keep double counting.

Any suggestions on formulas or data format would be great. Thanks for your help.

posted by Andy's Gross Wart to Computers & Internet (7 answers total)

If you are only interested in getting to 80 percent going from the most to least popular, you can sort them in descending fashion and add a column to the right (D) where

D3 (right of Product A) = C3 (that's 35%)

D4=D3+C4

Then drag D4 all the way down (hover your mouse over the lower right corner of D4 until the little black cross appears, then double click it or click and drag it down to the last row)

This way, column D is the cumulative percent. You could use conditional formatting to change the color of the cells that are >= 80%, or you can use an if function to do so: E3=if(D3>=80,A3,"")

Drag that down and only the cells >= 80% will appear in E

The difficulty here is that you may not have a cell that brings you exactly to 80% by simply going through a descending list. To try to get to exactly 80% without regard to strict order, you'd be trying to solve the knapsack problem
posted by i love cheese at 8:18 PM on June 15, 2009

Corrected link: The knapsack Problem
posted by i love cheese at 8:19 PM on June 15, 2009

Best answer: I did a screenshot of a spreadsheet.

It's in openoffice (I don't have Excel on this machine) so the ";" in the screenshot needs to be replaced with "," as below.

=IF(AND(SUM(C$2:C3)>=0.8,SUM(D$2:D2)=0),count(C$2:C3);0)

I think it'll work in Excel, but you may need to tweak it a little. I don't know if this is exactly what you want.
posted by selton at 2:06 AM on June 16, 2009

Yikes I made a mess of uploading that screenshot.

This is the correct screenshot, but I'm a newb with flickr so it's rather small...
posted by selton at 2:23 AM on June 16, 2009

Shouldn't the bit "SUM(D$2:D2)=0" be Sum () less than 0.8 not equal to zero?
posted by mary8nne at 3:07 AM on June 16, 2009

that screen shot is way too small i can't see anything.
posted by mary8nne at 3:08 AM on June 16, 2009

I have no idea why it worked this time and not before, but I uploaded the screenshot again and it's not tiny now.

non-tiny screenshot
posted by selton at 5:55 AM on June 16, 2009