How can I calculate sales quintiles?

shuksan

New Member
Joined
Jun 11, 2010
Messages
4
I have a list of 2000 product skus with sales volume from last year. I would like to break the skus into 5 equal groups, quintiles, with each group having 1/5th of the total sales volume. I'd like a formula that would insert "q1" or "q2", etc, so that I can sort on the quintiles and summarize them. Clearly, 20% of my skus generate 80% of my sales, and I would like to focus on them more!

I can do this manually with data sorting highest to lowest, calculating a % of total sales, adding up the cumulative sales %, and then labeling the quintiles, but I am looking for a automatic way to do this, as I want to review other data for each sku as well, such as inventory levels, gross margin, etc, and rank those items as well.

any ideas? sounds simple to me, but I've tried several things that dont' seem to work, including percentile, rank, large, etc...
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,144,155
Messages
5,722,818
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top