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

#### vletm

##### Excel Ninja
DWAGNER
Do `\$M\$8:\$M\$704` refer to 'Customer level' or where?
Only You could know that, because no even sample Excel-file!

#### DWAGNER

##### New Member
In the OP

I have list of items with annual revenue for the item captured in Column M and the customer name in Column C.

#### vletm

##### Excel Ninja
Yes, only You, DWAGNER, have lists ... then only You should know how to solve?
Did You skip my hint ( 2nd line )?

#### DWAGNER

##### New Member
Yes, only You, DWAGNER, have lists ... then only You should know how to solve?
Did You skip my hint ( 2nd line )?
Looking forward to an awesome answer

#### Attachments

• 40.2 KB Views: 1

#### vletm

##### Excel Ninja
DWAGNER
Do \$M\$8:\$M\$704 refer to 'Customer level' or where?
Your: Looking forward to an awesome answer

I don't have more ideas.

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