I need to calculate which test scores across 3 colleges are in the top 5% of scores,

[kylerisi]

I have a list of test scores that have been submitted by some students across 3 colleges. I need to indicate which of the test scores are in the top 5% of the scores that were submitted for each college.
I need to do this without needing to sort or group the data in the sheet.

Any help would be much appreciated.

College	Student Score	Count of scores submitted for college	Sum of scores for college	Student score is in the top 5% of scores submitted for college? YES/NO
B	48​	13​	729​
C	75​	11​	598​
B	27​	13​	729​
B	21​	13​	729​
B	56​	13​	729​
A	39​	10​	496​
B	81​	13​	729​
A	22​	10​	496​
C	49​	11​	598​
C	55​	11​	598​
C	90​	11​	598​
A	18​	10​	496​
C	40​	11​	598​
B	68​	13​	729​
A	82​	10​	496​
C	39​	11​	598​
B	68​	13​	729​
C	48​	11​	598​
A	47​	10​	496​
B	68​	13​	729​
B	66​	13​	729​
A	3​	10​	496​
B	68​	13​	729​
B	55​	13​	729​
B	66​	13​	729​
C	54​	11​	598​
C	69​	11​	598​
A	99​	10​	496​
A	17​	10​	496​
A	80​	10​	496​
C	20​	11​	598​
A	89​	10​	496​
C	59​	11​	598​
B	37​	13​	729​

 

[bebo021999]

College A has 10.
5% of 10 is 0.5
one student counts 1 ( = 1/10 = 10%)
How 1 student can be in top 5%?
Could you add manual output in column E?
I just give a shot to column E: E3 to count how many students in same college those score greater than that student in B3 (its also rank number).

Book1
	A	B	C	D	E
2	College	Student Score	Count of scores submitted for college	Sum of scores for college	Student score is in the top 5% of scores submitted for college? YES/NO
3	B	48	13	729	10
4	C	75	11	598	2
5	B	27	13	729	12
6	B	21	13	729	13
7	B	56	13	729	8
8	A	39	10	496	6
9	B	81	13	729	1
10	A	22	10	496	7
11	C	49	11	598	7
12	C	55	11	598	5
13	C	90	11	598	1
14	A	18	10	496	8
15	C	40	11	598	9
16	B	68	13	729	5
17	A	82	10	496	3
18	C	39	11	598	10
19	B	68	13	729	5
20	C	48	11	598	8
21	A	47	10	496	5
22	B	68	13	729	5
23	B	66	13	729	7
24	A	3	10	496	10
25	B	68	13	729	5
26	B	55	13	729	9
27	B	66	13	729	7
28	C	54	11	598	6
29	C	69	11	598	3
30	A	99	10	496	1
31	A	17	10	496	9
32	A	80	10	496	4
33	C	20	11	598	11
34	A	89	10	496	2
35	C	59	11	598	4
36	B	37	13	729	11
Sheet1

Cell Formulas
Range	Formula
E3:E36	E3	=SUMPRODUCT(($A$3:$A$36=A3)*($B$3:$B$36>=B3))

 

[Fluff]

Duplicate to: How to calculate the top 5% of numbers within a category

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.