When calculating the classwork grades, I sometimes need to calculate the average of the top N grades. For example, I asked the students to submit 11 problem sheets this semester; each is graded out of 10. Since the total grade assigned to the problem sheets is 10, I wanted to calculate the average of the top 8 grades for each student.
I calculate the average using the spreadsheet on Google Docs (equivalent to Excel). Last term, I sorted the grades of each student and I took the average of the first 8 cells. I didn't like this approach so I decided that I should come up with a formula for this calculation.
Here is the formula that I came up with. If anyone has a better formula, please share.
=(SUMIF(T6:AD6, ">="&PERCENTILE(T6:AD6, 1-8/COUNT(T6:AD6)))-(COUNTIF(T6:AD6,">="&PERCENTILE(T6:AD6, 1-8/COUNT(T6:AD6)))-8)*PERCENTILE(T6:AD6, 1-8/COUNT(T6:AD6)))/8
Note that the grades are in range T6:AD6. The number 8 can be changed to a cell reference if desired. If there is no repetition in the grades (not the general case), the formula simplifies to
=SUMIF(T6:AD6, ">="&PERCENTILE(T6:AD6, 1-8/COUNT(T6:AD6)))/8
So basically, I am summing up all the numbers that are larger than or equal to the eighth largest number and then dividing the sum by 8.
UPDATE
I got a shorter formula from my friend Raed Kafafy, which works just fine. Here it is:
=(SUMIF(T6:AD6,">"&LARGE(T6:AD6,8))+(8-COUNTIF(T6:AD6,">"&LARGE(T6:AD6,8)))*LARGE(T6:AD6,8))/8
UPDATE 2
I got an even shorter formula from my friend Ramy Harfoush.
=AVERAGE(LARGE(T6:AD6,{1,2,3,4,5,6,7,8}))"
Instead of writing 1 to 8 in an array, we can write the following formula and the press CTRL-SHIFT-ENTER.
=AVERAGE(LARGE(D2:N2,ROW(INDIRECT("1:8"))))
This way, we can only change the last digit of the list. This does not work on Google Spreadsheets.
-Seif Fateen