Assign "grading" based on multilpe criteria

[lostyankee]

I have a table of data with multiple fields that I need to include to assign a classification, or "grade" to. For example, in my data, I have:

• department
• floor set date
• region
• average rate of sale

I need to assign an "A" to those items who are in the top 30% of rate of sale for each department, floor set, and region "B" to the next 20%, "C" to next 30% and "D" to the remaining 20%.

[TABLE="width: 347"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Dept[/TD]
[TD]Floor Set Date[/TD]
[TD]Region[/TD]
[TD]Ave Rate of sales[/TD]
[TD]Ranking[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.44[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.44[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.4[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.33[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.29[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.28[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.24[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.21[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.15[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.13[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.08[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD]North[/TD]
[TD="align: right"]1.08[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]


Help!

 

[lostyankee]

I've seen threads on percentile ranking, but not sure I understand how to write that with this context.

=VLOOKUP(PERCENTRANK(IF($A$2:$A$7500=A2,IF($B$2:$B$7500=B2,$C$2:$C$7500)),C2),{0,1;0.25,2;0.5,3;0.75,4},2,1)

This was what I tried but couldn't get it to come back with any data.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Pctile​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Grade​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td]

0%​

[/td][td="bgcolor:#CCFFCC"]

1.080​

[/td][td]

D​

[/td][td="bgcolor:#CCFFCC"]D2: =PERCENTILE($D$8:$D$21, C2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td]

30%​

[/td][td="bgcolor:#CCFFCC"]

1.148​

[/td][td]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td]

50%​

[/td][td="bgcolor:#CCFFCC"]

1.260​

[/td][td]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td]

70%​

[/td][td="bgcolor:#CCFFCC"]

1.303​

[/td][td]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#F3F3F3"]

Dept​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

Region​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Grade​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.44​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td="bgcolor:#CCFFFF"]E8: =VLOOKUP(D8, $D$2:$E$5, 2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.44​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.40​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.33​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.30​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.29​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.28​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.24​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.21​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.15​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.13​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.10​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.08​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.08​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[/table]

 

[shg]

I'd do it as shown in the prior post, but with some redundant calculations, you can do it without the lookup table:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Dept​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

Region​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Grade​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.44​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td="bgcolor:#CCFFFF"]E2: =MID("DCBA", MATCH(D2, PERCENTILE($D$2:$D$15, {0,30,50,70}%), 1), 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.44​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.40​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.33​

[/td][td="bgcolor:#CCFFFF"]

A​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.30​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.29​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.28​

[/td][td="bgcolor:#CCFFFF"]

B​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.24​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.21​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.15​

[/td][td="bgcolor:#CCFFFF"]

C​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.13​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.10​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.08​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

123​

[/td][td]

5/1/2019​

[/td][td]North[/td][td]

1.08​

[/td][td="bgcolor:#CCFFFF"]

D​

[/td][td][/td][/tr]
[/table]

 

[lostyankee]

My example I pasted in was poor... my fault. The department, set date, region are all variables in the formula, the range is over 7000 rows long with more than 50 departments, 8 set dates and 6 regions. I do love the assist by SHG on using the MID function in a way I hadn't tried... so kudos there.

So how do you get the "grade" if these three columns are variables in the range?