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.