How to average the top N values in Excel

AVERAGE with LARGE and an array constant

The sheet is a quiz gradebook where each student's grade is the average of their best 3 of 5 quizzes. LARGE(range,k) returns the k-th largest number. Give it the array constant {1,2,3} and it returns the three largest at once, which AVERAGE then averages. This works in every Excel version and needs no Ctrl+Shift+Enter, because the constant is typed into the formula.

It reads across a row just as well as down a column, so fill it down to grade every student. Wrap it in ROUND(...,1) if the gradebook shows one decimal.

Keep N in a cell (Excel 365 and 2021)

Hard-coding {1,2,3} means editing every formula when the rule changes to best 4. Put N in a cell (I1 here) and build the list of ranks with SEQUENCE: SEQUENCE(3) is {1;2;3}. Lock the cell with $ so it stays put when you fill down.

In Excel 2019 and earlier there is no SEQUENCE. The classic replacement is ROW(INDIRECT("1:"&$I$1)) inside LARGE, confirmed with Ctrl+Shift+Enter. It works, but INDIRECT is volatile, so on a big gradebook prefer typing the constant.

Show which values were kept: SORT and TAKE (Excel 365)

When a student asks which quizzes counted, show the kept scores next to the grade. SORT(B2:F2,,-1,TRUE) sorts the row largest first (TRUE sorts across columns), and TAKE(...,,3) keeps the first three columns. Use $I$1 instead of the 3 to follow the N cell.

Wrap the same thing in AVERAGE for the grade: =AVERAGE(TAKE(SORT(B2:F2,,-1,TRUE),,3)) returns 89.67 for Maya, matching the LARGE version. For a column of data, drop the column arguments: TAKE(SORT(B2:B20,,-1),3) keeps the first three rows. TAKE is in Excel 365 and Excel 2024, not in Excel 2021, so stick with LARGE in files that travel.

Fewer than N values: avoid #NUM!

Zara has only two numeric scores. The "Absent" is text and LARGE ignores it, and so is an empty cell. Asking for the 3rd largest of two numbers gives #NUM!, and AVERAGE passes the error on. This is the error people hit most, usually for a new student or a short first month.

Decide what the rule should be. To average whatever is there, cap N at the number of values with MIN(N,COUNT(range)). To flag the row instead, use =IFERROR(AVERAGE(LARGE(B4:F4,{1,2,3})),"Not enough scores"). A blank counts as no score, but a typed 0 is a real score: if missed quizzes must count as zero, type 0 in them rather than leaving them empty.

Ties: LARGE counts duplicates, AVERAGEIF does not stop at N

Ines scored 85 three times. LARGE treats each 85 as its own value, so her top 2 are 91 and 85, and =AVERAGE(LARGE(B6:F6,{1,2})) gives 88. That is what "best 2 scores" means, and it is always exactly N values.

A common shortcut is to average everything at or above the N-th largest: =AVERAGEIF(B6:F6,">="&LARGE(B6:F6,2)). With ties at the cut-off it averages more than N values. Here it takes 91 and all three 85s, which is 4 values, not 2.

Bottom N, and dropping the lowest score

SMALL is the mirror of LARGE: =AVERAGE(SMALL(B5:F5,{1,2})) averages the two lowest values, useful for finding the weakest results. Zeros are numbers, so Leo's 0 counts. To leave zeros out, filter them first: =AVERAGE(SMALL(IF(B5:F5>0,B5:F5),{1,2})), entered with Ctrl+Shift+Enter in Excel 2019 and earlier.

"Drop the lowest score" is the same as averaging the top N where N is the count minus one, and it adapts when a student has fewer quizzes. In any version you can also write it as =(SUM(B5:F5)-MIN(B5:F5))/(COUNT(B5:F5)-1).

Which method to pick

A fixed rule like best 3 in any Excel version: AVERAGE(LARGE(range,{1,2,3})). A rule people change, in Excel 365 or 2021: SEQUENCE with N in a cell, capped with MIN and COUNT if some rows are short. Drop the lowest: SUM minus MIN, divided by COUNT minus one. To see which values were kept, list them with LARGE(range,SEQUENCE(N)) in Excel 365, or sort a copy. Skip the AVERAGEIF shortcut whenever ties are possible, which is any time scores are whole numbers.