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