MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create 80/20 Rule?


Posted by Mandy on August 16, 2001 2:36 AM

How do I go about creating from a list of customers, a listing showing which customers contribute to 80% of our business?
Thank you for any help or advice
Mandy


Posted by Aladin Akyurek on August 16, 2001 3:39 AM

> How do I go about creating from a list of customers, a listing showing which customers contribute to 80% of our business?

Mandy,

Consider the following sample data

{"Customer","$ value";
"mandy",100;
"cindy",200;
"aladin",10;
"damon",40}

in A4:A8.

In A1 and A2 enter the following labels:

{"Total";
"80% of Total";
"StartRow"}

In B1 enter: =SUM(B4:B7)
In B2 enter: =B1*80%
In B3 enter: =ROW(4:4)

In C5 enter: =IF($B$1-B5<$B$2,MAX($C$1:C4)+1,"") [ copy down this as far as needed ]

Note. The range C1:C4 must be empty!

In D5 enter: =IF(ISNUMBER(MATCH(ROW()-$B$3,$C$5:$C$8,0)),INDEX($A$5:$A$8,MATCH(ROW()-$B$3,$C$5:$C$8,0)),"") [ copy down as far as needed ]

You'll get the list of VIC's you are interested it.

If you need assistence to adopt the above scheme to your situation, let me know.

Aladin

Posted by Mark W. on August 16, 2001 7:10 AM

You could also...

...create a pareto chart from your data using a
combination chart consisting of ordered columns
and a cummulative percentage line. When examining
such a chart your merely locate the 80% point
and identify all of the categories on the X-axis
that are to the left of that point.