Pecentile, Ranking, or something else?

[ChaplainLewis]

I have a list that ranges from 200-225 rows long and constantly in flux. In each row is a persons name, a score of sorts, and a grade. There's more than that but it doesn't really matter for this question. I might look something like this:

	A	B	C
1	Name	Score	Grade
2	Tom	5.5
3	Sally	3.2
4	Ed	1.6
5	Alice	4.9
6	Esther	2.8
7	Jerry	5.4
8	Lenny	4.6
9	Delores	1.6
10	Scott	3.2
11	Craig	3.7

<tbody>
</tbody>

The real issue is the grade. Imagine if this table were several hundred rows deep. I am hoping to get the grade to populate based on the score as ordered into the overall list (something like a percentile, I think). The grading would be like this:

	A	B
1	Grade	Percent
2	A	Top 10%
3	P	Next 24%
4	C	Next 33%
5	M	Bottom 33%

<tbody>
</tbody>

After sorting and adding the grade manually, it has gotten more than a little tedious. I'm hoping I can automate it. I've tried nested IF statements and messed around with the PERCENTILE.INC function (which was an interesting failure). I've even used RANK and a couple other functions I'm not terribly familiar with. I get close but can't seem to make it work. Any help would be appreciated.

Thanks a ton.

 

[63falcondude]

How about something like this?

Excel 2010
	A	B	C	D	E	F	G
1	Name	Score	Grade		Grade	Percent
2	Tom	5.5	A		A	Top 10%	0 to 10
3	Sally	3.2	C		P	Next 24%	11 to 34
4	Ed	1.6	M		C	Next 33%	35 to 64
5	Alice	4.9	P		M	Bottom 33%	65 to 100
6	Esther	2.8	M
7	Jerry	5.4	P
8	Lenny	4.6	C
9	Delores	1.6	M
10	Scott	3.2	C
11	Craig	3.7	C
Sheet1

Cell Formulas
Range	Formula
C2	=LOOKUP(RANK(B2,$B$2:$B$11)/COUNT($B$2:$B$11),{0,0.11,0.35,0.65},{"A","P","C","M"})

 

[ChaplainLewis]

I'll give that a shot. But if I may ask a couple of follow on questions based on your answer, just so I can understand what's actually happening here.

1. I've never seen the "{" symbol used. Is that shorthand for something or do I use that exactly?
2. Instead of the "{0,0.11,0.35,0.65}" can I substitute the reference such as "{E2,E3,E4,E5}"? that would allow me to change the percentages if my boss ever wanted a different scale.

Again, thanks a ton.

 

[63falcondude]

I'll give that a shot. But if I may ask a couple of follow on questions based on your answer, just so I can understand what's actually happening here.

1. I've never seen the "{" symbol used. Is that shorthand for something or do I use that exactly?
2. Instead of the "{0,0.11,0.35,0.65}" can I substitute the reference such as "{E2,E3,E4,E5}"? that would allow me to change the percentages if my boss ever wanted a different scale.

Again, thanks a ton.

The curly brackets are essentially doing this without having the lookup table. If you might change the values in the future, having a lookup table seems like the way to go.

Excel 2010
	A	B	C	D	E	F	G
1	Name	Score	Grade		Grade	Percent	Description
2	Tom	5.5	A		A	0%	0 to 10
3	Sally	3.2	C		P	11%	11 to 34
4	Ed	1.6	M		C	35%	35 to 64
5	Alice	4.9	P		M	65%	65 to 100
6	Esther	2.8	M
7	Jerry	5.4	P
8	Lenny	4.6	C
9	Delores	1.6	M
10	Scott	3.2	C
11	Craig	3.7	C
Sheet1

Cell Formulas
Range	Formula
C2	=LOOKUP(RANK(B2,$B$2:$B$11)/COUNT($B$2:$B$11),$F$2:$F$5,$E$2:$E$5)