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

chieh

New Member
Joined
Feb 20, 2011
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • Example.gif
    Example.gif
    33.6 KB · Views: 6

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

chieh

New Member
Joined
Feb 20, 2011
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Thanks everyone, I got it figured out. I have attached a screen shot to share.

:)
 

Attachments

  • Example2.gif
    Example2.gif
    41.7 KB · Views: 17

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
AB
1StudentCorrect Answer Count
2Student 1200
3Student 275
4Student 3374
5Student 4139
6Student 5277
7Student 6185
8Student 72
9Student 80
10Student 91
11Student 1094
12Student 11235
13Student 1255
14Student 1327
15Student 14121
16Student 1578
17Student 16111
18Student 1712
19Student 18117
20Student 196
21Student 204
22Student 2187
23Student 2265
24Student 2323
25Student 2414
26Student 25200
27Student 26100
28Student 275
29Student 2827
30Student 293
31Student 3065
32Student 3116
33
34Top 20%245.1666667
35Middle 60%64.84210526
36Bottom 20%2.5
Averages
Cell Formulas
RangeFormula
B34B34=AVERAGEIF(B2:B32,">"&PERCENTILE(B2:B32,0.8),B2:B32)
B35B35=AVERAGEIFS(B2:B32,B2:B32,"<="&PERCENTILE(B2:B32,0.8),B2:B32,">="&PERCENTILE(B2:B32,0.2))
B36B36=AVERAGEIF(B2:B32,"<"&PERCENTILE(B2:B32,0.2),B2:B32)
 

chieh

New Member
Joined
Feb 20, 2011
Messages
7
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 20, 2011
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
.. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top