Percentile for particular cells

[padraic]

Hi there - I'm a bit of an Excel newbie and I haven't managed to solve my query by looking at other forum posts. I'm probably going to describe this issue quite poorly - any help/corrections much appreciated!


I have a list - each item has an individual rating out of 10. Next to each rating I'd like a figure that gives me an idea of how good that rating is compared to all the other ratings. I think percentile is what I'm after. For example, next to one rating I could see something like 95%, which tells me that this rating is better than 95% of all the others. Or it could be 5% - meaning it's in the top 5% of all the ratings. (I'm not sure whether the percentage symbol will appear - but even something like 0.95 or 0.05 would be fine).


Am I making any sense? Thanks so much for your help!

 

[oldbrewer]

score
1	terrible						min	1
5	average						max	10
2	terrible							m y t a b l e
9	excellent						ranges	1	2	terrible
3	poor							3	4	poor
5	average							5	6	average
5	average							7	8	good
6	average							9	10	excellent
7	good
8	good
10	excellent
3	poor
4	poor
3	poor
2	terrible
1	terrible
8	good
6	average
8	good
7	good
		B2
		=VLOOKUP(A2,mytable,3)

<colgroup><col span="7"><col><col span="4"></colgroup><tbody>
</tbody>

 

[padraic]

Thanks! I like this method. It's a little more straightforward than looking at a percentage. But I guess what I'm after isn't so much about assigning static levels to particular scores - more about seeing how each score fits into the overall data set. For example, 8/10 is clearly good - but I'd like to see exactly how exceptional it is in the context of all the other scores. The data set will grow continually as more and more ratings are added, so these percentages will change over time. I might remember, for example, a time when 8/10 was better than 75% of all results, but now I can see it's only better than 70% of all results.

I've actually cross-posted (sorry!) and have now learned about the issues connected with it. The original post is on this forum https://www.excelforum.com/excel-fo...centile-for-particular-cells.html#post4701732

 

[inactiveuserps07]

@padriac, assuming your individual scores are entered from A2 down:

1. Create a table, say in C1:D11 (used in my example). Headers in C1 and D1: Score and %-ile Rank respectively. In C2:C11, enter the numbers 1 through 10, one in each cell.

2. In D2, enter the following formula and then drag-copy to D11:

=(COUNTIF($A$2:$A$2000,"<="&C2)/COUNT($A$2:$A$2000))*100

3. Select D2:D11 and set the Number Format as desired (e.g., to one decimal place, to two decimal places, etc.). If you want to format this range as Percentage to begin with (in order to see the % symbol in each cell), just omit the *100 at the end of the formula.

If you want to see the non-cumulative distribution only (whether in Column D or by adding a Column E for "% Dist"), just omit the < symbol from the formula.

 

[oldbrewer]

									worse than	better than
1		how many results	20		1	2	2	90	0
5						2	2	4	80	10
2						3	3	7	65	20
9						4	1	8	60	35
3						5	3	11	45	40
5						6	2	13	35	55
5						7	2	15	25	65
6						8	3	18	10	75
7						9	1	19	5	90
8						10	1	20	0	95
10
3
4
3
2
1
8						considering 8 out of 10
6
8						15 out of 20 are below it
7
						15/20 = 75%
						so 8 out of 10 is better than 75% of the results
						and of course is worse than 2 out of 20 = 10%
						so add two more columns as above
						E2 (20)
						=COUNTA(A2:A1000)
						J2 (below worse than)
						=100*(($E$2-I2)/$E$2)
						K2 (below better than)
						0
						K3
						=100*I2/$E$2
						now as you add more scores the stats remain accurate

<colgroup><col span="9"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[oldbrewer]

							count		worse than	better than
1		how many results	20		1	2	2	90	0
5						2	2	4	80	10
2						3	3	7	65	20
9						4	1	8	60	35
3						5	3	11	45	40
5						6	2	13	35	55
5						7	2	15	25	65
6						8	3	18	10	75
7						9	1	19	5	90
8						10	1	20	0	95
10
3
4						H2 (below count)
3						=COUNTIF($A$2:$A$21,G2)
2
1
8						considering 8 out of 10
6
8						15 out of 20 are below it
7
						15/20 = 75%
						so 8 out of 10 is better than 75% of the results
						and of course is worse than 2 out of 20 = 10%
						so add two more columns as above
						E2 (20)
						=COUNTA(A2:A1000)
						J2 (below worse than)
						=100*(($E$2-I2)/$E$2)
						K2 (below better than)
						0
						K3
						=100*I2/$E$2
						now as you add more scores the stats remain accurate

<colgroup><col span="9"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[padraic]

Thank you, both! Very helpful.