I encountered this problem when trying to get the final grades for an course I TAed for this semester. There were 10 homework assignments throughout the semester, and we're supposed to only count the 8 highest grades. So, how to accomplish this in Google Spreadsheet?
First Try
After poking around Google search results a little bit, I found this solution, which seems to work.
=ceiling(sum(filter(E2:N2,E2:N2>=large(E2:N2, 8)))/8,1)
Where E2:N2 contains the 10 grades. The large function will return
the 8th highest grade of the 10, and then we only sum the grades that larger
than or equal to that grade.
This seems all fine until I accidentally found that some students got more than 100 pts, which is impossible because all our grades are 100 based!
The Problem
Well, what's wrong with the previous formula? Suppose a student's 10 grades look like this
94 97 92 94 98 100 100 100 100 100
Sort them in descending order
1 2 3 4 5 6 7 8 9 10
100 100 100 100 100 98 97 94 94 92
So in this case the 8th largest number is 94, yet there are two 94, and we really just need one of them.
The Solution
After struggling in Google Spreadsheet function list, I found that we can actually do SQL-like queries within the spreadsheet! This leads to the final solution.
=ceiling(sum(query(sort(transpose(E2:N2), 1, FALSE), "select * limit 8"))/8,1)
Here we first transpose the row data into column, then sort them in descending order, then we just take the first 8 grades when calculating the average.