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

[kylerisi]

Hi Team.

Can anyone please help me construct the following Formula.

In collum B I have the scores of a test that was taken by 100 students. In collum A is the college that each student belongs to, there are 10 colleges. All scores are in a random order, not sorted by rank or grouped by college, scores and the college are scattered randomly throughout my list in order that they were submitted.

I need a formula that I can enter into collum C which will calculate which scores for each college are in the top 20%, I need to do this without sorting my list.

Can this be done.

 

[KRice]

What version of Excel are you using?...you can update your profile to show that.

 

[kylerisi]

What version of Excel are you using?...you can update your profile to show that.

Ahh, so sorry, im using Office 365

 

[KRice]

Let me know if this works...or if you have something else in mind. My mock-up extends down to row 103. The key steps are to find the 80th percentile of the scores (cell B1) and then use that value to test each score to find the ones that are >=...which is shown in D4 and down. If you'd like, you could extract just the top 20% and list them separately, but I'm not sure what you have in mind.

MrExcel_20220808.xlsx
	A	B	C	D	E
1		86.4	<--80th percentile	20	<--count
2
3	School	Score	Student Name	Top 20% (shown as 1)
4	E1	56	1	0
5	A	52	2	0
6	C1	69	3	0
7	D	53	4	0
8	B	38	5	0
9	E	52	6	0
10	D	51	7	0
11	C	75	8	0
12	E1	86	9	0
13	A1	81	10	0
14	C1	92	11	1
15	D	93	12	1
16	B	45	13	0
17	D	51	14	0
18	B1	75	15	0
19	E	56	16	0
20	B1	98	17	1
Sheet3

Cell Formulas
Range	Formula
B1	B1	=PERCENTILE.INC(B4:B103,0.8)
D1	D1	=SUM(D4:D103)
D4:D20	D4	=--(B4>=$B$1)

 

[KRice]

As an example, the single formula in the yellow cell will perform the percentile calculation, make the logical tests to determine which scores qualify, and then filter the results to extract the top 20% of scores and then sort them, first by school in ascending order, followed by score in descending order.

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H
1		86.4	<--80th percentile	20	<--count
2
3	School	Score	Student Name	Top 20% (shown as 1)		School	Score	Student Name
4	E1	56	1	0		A	91	95
5	A	52	2	0		A	88	98
6	C1	69	3	0		A1	99	43
7	D	53	4	0		A1	90	64
8	B	38	5	0		B	89	42
9	E	52	6	0		B1	98	17
10	D	51	7	0		B1	98	45
11	C	75	8	0		C	99	62
12	E1	86	9	0		C	99	72
13	A1	81	10	0		C1	92	11
14	C1	92	11	1		C1	91	57
15	D	93	12	1		C1	91	67
16	B	45	13	0		D	93	12
17	D	51	14	0		D1	94	22
18	B1	75	15	0		D1	90	60
19	E	56	16	0		E	97	76
20	B1	98	17	1		E	95	61
21	A	46	18	0		E1	97	31
22	C	33	19	0		E1	90	34
Sheet3

Cell Formulas
Range	Formula
B1	B1	=PERCENTILE.INC(B4:B103,0.8)
D1	D1	=SUM(D4:D103)
F4:H23	F4	=SORT(FILTER(A4:C103,B4:B103>=PERCENTILE.INC(B4:B103,0.8)),{1,2},{1,-1})
D4:D22	D4	=--(B4>=$B$1)
Dynamic array formulas.

 

[KRice]

I'm confused by the title of this thread and your first post. I've assumed you want the top 20% of scores when aggregating across all schools. The title mentions top 5% within a category. What are the categories?...is it top 20% or top 5%.