Dynamic Excel Formula to Find Average of top 20%, bottom 20% and middle 60%

[chieh]

Hello:

I have an existing formula where I calculate the average of top 5, bottom 5 and middle X of a data set, for example student's correct answer. The formula like this...

Sample Data:

Student	Correct Answer Count​
Student 1	200​
Student 2	75​
Student 3	374​
Student 4	139​
Student 5	277​
Student 6	185​
Student 7	2​
Student 8	0​
Student 9	1​
Student 10	94​
Student 11	235​
Student 12	55​
Student 13	27​
Student 14	121​
Student 15	78​
Student 16	111​
Student 17	12​
Student 18	117​
Student 19	6​
Student 20	4​
Student 21	87​
Student 22	65​
Student 23	23​
Student 24	14​
Student 25	200​
Student 26	100​
Student 27	5​
Student 28	27​
Student 29	3​
Student 30	65​
Student 31	16​
Top 5 Avg.	257​
Middle 21 Avg.	68​
Bottom 5 Avg.	2​

Formula For:
Top 5 avg: = SUM(LARGE($B$2:B$32,{1,2,3,4,5}))/5
Middle x avg: = (SUM(B$2:B$32) - (SUM(LARGE($B$2:B$32,{1,2,3,4,5})) + SUM(SMALL($B$2:B$32,{1,2,3,4,5}))) ) / (COUNTA(B$2:B$32)-5-5)
Bottom 5 avg: = SUM(SMALL($B$2:B$32,{1,2,3,4,5}))/5

Since my list of students can be more or less, i,e, some class can have 30 students, some can have 50, and some can have 100, I am trying to determine a dynamic formula to determine the following, i.e.

Top 20% Avg.
Middle 60% Avg.
Bottom 20% Avg.

Using my existing formula for Top 5 Avg, Bottom 5 Avg and Middle x avg, how can I make the formula dynamic based on % instead of actual fixed #

Thank you in advance.

Regards
Chieh

 

[cyrilbrd]

Please have a look at the solution proposed by Robert Mika in this thread:
Calculate top 20% of values from a list, sum product

 

[chieh]

Thanks everyone, I got it figured out. I have attached a screen shot to share.

 

[Peter_SSs]

Not sure what Excel version you have (I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.) but this may also be of use? (Colour was just for my checking)

20 02 26.xlsm
	A	B
1	Student	Correct Answer Count
2	Student 1	200
3	Student 2	75
4	Student 3	374
5	Student 4	139
6	Student 5	277
7	Student 6	185
8	Student 7	2
9	Student 8	0
10	Student 9	1
11	Student 10	94
12	Student 11	235
13	Student 12	55
14	Student 13	27
15	Student 14	121
16	Student 15	78
17	Student 16	111
18	Student 17	12
19	Student 18	117
20	Student 19	6
21	Student 20	4
22	Student 21	87
23	Student 22	65
24	Student 23	23
25	Student 24	14
26	Student 25	200
27	Student 26	100
28	Student 27	5
29	Student 28	27
30	Student 29	3
31	Student 30	65
32	Student 31	16
33
34	Top 20%	245.1666667
35	Middle 60%	64.84210526
36	Bottom 20%	2.5
Averages

Cell Formulas
Range	Formula
B34	B34	=AVERAGEIF(B2:B32,">"&PERCENTILE(B2:B32,0.8),B2:B32)
B35	B35	=AVERAGEIFS(B2:B32,B2:B32,"<="&PERCENTILE(B2:B32,0.8),B2:B32,">="&PERCENTILE(B2:B32,0.2))
B36	B36	=AVERAGEIF(B2:B32,"<"&PERCENTILE(B2:B32,0.2),B2:B32)

 

[chieh]

Thank you Peter.

I am using Excel 2013/
Looks like your formula of AverageIF and Percentile is better and less code.

Regards
Chieh.

 

[Peter_SSs]

Looks like your formula of AverageIF and Percentile is better and less code.

.. but you don't have the AVERAGEIFS function? You could use this array formula (confirmed with Ctrl+Shift+Enter, not just Enter) instead.

=AVERAGE(IF(B2:B32<=PERCENTILE(B2:B32,0.8),IF(B2:B32>=PERCENTILE(B2:B32,0.2),B2:B32)))

 

[chieh]

.. but you don't have the AVERAGEIFS function? You could use this array formula (confirmed with Ctrl+Shift+Enter, not just Enter) instead.

=AVERAGE(IF(B2:B32<=PERCENTILE(B2:B32,0.8),IF(B2:B32>=PERCENTILE(B2:B32,0.2),B2:B32)))

Thanks Peter,

I am using Excel 2013, and it has the AverageIFs() function. Your formula works perfectly.

Cheers and regards,
Chieh

 

[Peter_SSs]

I am using Excel 2013, and it has the AverageIFs() function.

Ah, sorry about that. Another case where the Microsoft Help appears to be deficient

AVERAGEIFS function
Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2019 for Mac Excel Web App Less