Statistical
Average top 3 (or top N) scores: drop lowest grades dynamically
Combine SORT, TAKE, and AVERAGE to average the highest N scores—with N driven by a cell instead of a fixed {1,2,3} constant.
Why TAKE + SORT beats hardcoded LARGE(range, {1,2,3})
Course syllabi and sales contests frequently average each person's best 3 quizzes (automatically dropping their lowest 2 scores). Writing AVERAGE(LARGE(B2:B6, {1,2,3})) hardcodes 3 inside an array literal that cannot reference a cell.
Sorting descending with SORT(B2:B6, 1, -1) and slicing the top D2 rows with TAKE(..., D2) lets you change Top-3 to Top-4 by editing a single parameter cell!
| Quiz (A) | Score (B) | Sorted Rank | TopN (D2) |
|---|---|---|---|
| Quiz 1 | 72 | #4 (dropped) | 3 |
| Quiz 2 | 94 | #1 (kept) | |
| Quiz 3 | 88 | #3 (kept) | |
| Quiz 4 | 65 | #5 (dropped) | |
| Quiz 5 | 91 | #2 (kept) |
Average the highest N scores
The top 3 scores are 94, 91, and 88, whose average is (94 + 91 + 88) / 3 = 91.
=ROUND(AVERAGE(TAKE(SORT(B2:B6, 1, -1), D2)), 2)Average the bottom N scores or guard N < 1
Flipping sort_order to 1 averages the lowest N scores ((65 + 72) / 2 = 68.5), and IF(D2<1, ...) prevents TAKE(..., 0) #CALC! errors.
=AVERAGE(TAKE(SORT(B2:B6, 1, 1), 2))Try the example
Use the original values or change them to test your understanding.