Percentile Ranking of Subtotals

[CNDNewbie]

Hi there!

I have a data set that I need to subtotal. Within each subtotal, I would like to highlight the top 80% of SKUs. The data is not sorted when downloaded, and can be sorted. Because this data set may grow over time, I am thinking it may make sense to write code, although if I can do this with a formula I would appreciate it. Please see attached for a sample data set. In reality, this data set will be about 2,400 rows.

Let me know if there are any further questions.

 

[vletm]

CNDNewbie
Did You missed to write
sample result of subtotals and
highlights of the top 80% of SKUs ( whatever it would be) ?

 

[CNDNewbie]

Vletm -

Yes - I know how to subtotal however, I am not sure how to highlight the 80% percentile without manually going through each subtotal. I am expecting to have to do this every month, with new item codes added each month so I wanted a quick way to run this process.

Thanks.

CNDNewbie
Did You missed to write
sample result of subtotals and
highlights of the top 80% of SKUs ( whatever it would be) ?

 

[vletm]

CNDNewbie
Once a again ...
Did You missed to write
sample result of subtotals and
highlights of the top 80% of SKUs ( whatever it would be) ?
If You know how to subtotal then try to do sample results
and
as well sample how would You like to see those others.
Especially, to show, what would be SKU.

 

[CNDNewbie]

Hi there -
So here is how the process should go:
-Receive data set
-Sort data by category
-Add subtotals with sum at each month with change in category
-Determine 70% percentile for each subtotal
-Highlight rows within subtotals that fall in 70% or above

I did this manually and have attached what the result should look like. Is this doable with a formula or will I need to create a macro? How do I make this dynamic enough when the data set changes due to introduction of new item codes?

Thanks for your help!

CNDNewbie
Once a again ...
Did You missed to write
sample result of subtotals and
highlights of the top 80% of SKUs ( whatever it would be) ?
If You know how to subtotal then try to do sample results
and
as well sample how would You like to see those others.
Especially, to show, what would be SKU.

 

[CNDNewbie]

Also wanted to add that I added the value in cell P3 - the percentile formula that calculates what the 70% percentile is for the range of data. I then proceeded to highlight any items larger than that number.

 

[vletm]

CNDNewbie
I would do it something like this ...
by pressing [ Do It ]-button.
hmm ... Your 80% changed to 70% ...

 

[CNDNewbie]

Vletm -
Hi there. I think we are on the right path. So there should be at least one item code per subtotal that is highlighted. Let's do the percentile based on full year subtotals by category. I added a column called Full Year (Column P) - sorry, should have added that earlier. The result should highlight the item codes that fall at or above the 70% percentile. So for the CATONE subtotal, item codes 106 and 112 should be highlighted as they fall at or above the 70% percentile.

Thank you for your help so far!

 

[vletm]

CNDNewbie
I don't know about Your path...
If some item codes won't fall at or above the 70% percentile,
should code add value as much as needed to get big enough value or what?

 

[CNDNewbie]

Hi Vletm -

No the code should not add value. Percentile is a straight calculation (e.g. =PERCENTILE(SUBTOTAL,.70). The item codes fall either above or below the resulting value.

Hope this helps and thank you for your help!

 

[vletm]

CNDNewbie
... maybe You answered to 'what'?