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...
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...
