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.