• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentile Selection by Subtotal

DWAGNER

New Member
I have list of items with annual revenue for the item captured in Column M and the customer name in Column C. I need to classify each item by both item level and customer level (separate results are fine) into either A or B values, resulting in four possible combinations (AA, AB, BA, BB).

I need to utilize this classification matrix by percentile:
80% Customer = A
20% Customer = B
80% Item = A
20% Item = B

Item classification appears to be working fine using this formula: IF($M13>PERCENTILE($M$8:$M$704,20%),"A","B")

I've been searching and trying multiple other iterations for the Customer classification without success. The A/B value will still exist on the Item record so that it can be used for filtering and pivot tables.

I've attempted to utilize the Customer total within the context of the Percentile function but it's not working properly.

IF(SUMPRODUCT(($C$8:$C$704=$C13)*($M$8:$M$704))/COUNTIF($C$8:$C$704,$C13)>PERCENTILE($M$8:$M$704,20%),"A","B")

I'm certain that my problem lies within: PERCENTILE($M$8:$M$704,20%),"A","B") but I'm uncertain how to modify the formula to look at customer totals versus item detail.

Any assistance you can provide would be greatly appreciated.
 

vletm

Excel Ninja
DWAGNER
Have You checked ... what are result of these?
A = SUMPRODUCT(($C$8:$C$704=$C13)*($M$8:$M$704))
B = COUNTIF($C$8:$C$704,$C13)
C = PERCENTILE($M$8:$M$704,20%)
after that, try Your formula =If( A / B > C,"A","B")
 

DWAGNER

New Member
Great suggestion. I did do so prior to posting. That's how I discovered that my problem lies within the "C" portion of the formula - it's returning the Item level percentile value - I need the Customer level percentile value but don't know how to calculate it using the Item level record set.

C = PERCENTILE($M$8:$M$704,20%)

Thanks so much for reply.
 

DWAGNER

New Member
It's a redacted worksheet due to customer sensitive information. OP referred to my working data set.

Customer $A$2:$A$698
Item $B$2:$B$698
Revenue $C$2:$C$698
Awesome Answer $D$2:$D$698
 
Top