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:
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
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