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

Dept	Floor Set Date	Region	Ave Rate of sales	Ranking
123	5/1/2019	North	1.44	A
123	5/1/2019	North	1.44	A
123	5/1/2019	North	1.4	A
123	5/1/2019	North	1.33	A
123	5/1/2019	North	1.3	A
123	5/1/2019	North	1.29	A
123	5/1/2019	North	1.28	A
123	5/1/2019	North	1.24	A
123	5/1/2019	North	1.21	A
123	5/1/2019	North	1.15	B
123	5/1/2019	North	1.13	B
123	5/1/2019	North	1.1	B
123	5/1/2019	North	1.08	B
123	5/1/2019	North	1.08	B

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

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]

	A​	B​	C​	D​	E​	F​
1​			Pctile​	Rate​	Grade​
2​			0%​	1.080​	D​	D2: =PERCENTILE($D$8:$D$21, C2)
3​			30%​	1.148​	C​
4​			50%​	1.260​	B​
5​			70%​	1.303​	A​
6​
7​	Dept​	Date​	Region​	Rate​	Grade​	​
8​	123​	5/1/2019​	North	1.44​	A​	E8: =VLOOKUP(D8, $D$2:$E$5, 2)
9​	123​	5/1/2019​	North	1.44​	A​
10​	123​	5/1/2019​	North	1.40​	A​
11​	123​	5/1/2019​	North	1.33​	A​
12​	123​	5/1/2019​	North	1.30​	B​
13​	123​	5/1/2019​	North	1.29​	B​
14​	123​	5/1/2019​	North	1.28​	B​
15​	123​	5/1/2019​	North	1.24​	C​
16​	123​	5/1/2019​	North	1.21​	C​
17​	123​	5/1/2019​	North	1.15​	C​
18​	123​	5/1/2019​	North	1.13​	D​
19​	123​	5/1/2019​	North	1.10​	D​
20​	123​	5/1/2019​	North	1.08​	D​
21​	123​	5/1/2019​	North	1.08​	D​

 

[shg]

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

	A​	B​	C​	D​	E​	F​
1​	Dept​	Date​	Region​	Rate​	Grade​	​
2​	123​	5/1/2019​	North	1.44​	A​	E2: =MID("DCBA", MATCH(D2, PERCENTILE($D$2:$D$15, {0,30,50,70}%), 1), 1)
3​	123​	5/1/2019​	North	1.44​	A​
4​	123​	5/1/2019​	North	1.40​	A​
5​	123​	5/1/2019​	North	1.33​	A​
6​	123​	5/1/2019​	North	1.30​	B​
7​	123​	5/1/2019​	North	1.29​	B​
8​	123​	5/1/2019​	North	1.28​	B​
9​	123​	5/1/2019​	North	1.24​	C​
10​	123​	5/1/2019​	North	1.21​	C​
11​	123​	5/1/2019​	North	1.15​	C​
12​	123​	5/1/2019​	North	1.13​	D​
13​	123​	5/1/2019​	North	1.10​	D​
14​	123​	5/1/2019​	North	1.08​	D​
15​	123​	5/1/2019​	North	1.08​	D​

 

[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?