How to average excluding the highest and lowest values in Excel

Q: Is there a built-in function for this?

TRIMMEAN drops a percentage of values from both ends: =TRIMMEAN(B2:F2,0.4) drops 40% of 5 values, one from each end.

Q: How do I drop the two highest and two lowest?

Average the middle ranks. With seven judges in B2:H2: =AVERAGE(LARGE(B2:H2,{3,4,5})).

Formula (Excel and Google Sheets)

=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)

Take the highest and lowest out of the total and divide by two fewer: =(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2).

Result on the example: one result per row in G2, filled down to G5
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in G2, filled down to G5. Change any input and the result updates, or edit the formula to experiment.

How it works

The total of the five marks, minus the highest and the lowest, leaves the middle three; dividing by COUNT-2 averages them. This is how judged sports keep one generous or harsh judge from deciding the result. If two judges tie for highest, only one of them is dropped.

=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)

Result7.666666667

B2:F21 row × 5 cols: 7.5, 8, 7, 8.5, …
A range: 5 cells in one row.
SUM(B2:F2)38.5
SUM: Adds numbers and ranges. Skips text and blanks.
- number1B2:F21 row × 5 cols: 7.5, 8, 7, 8.5, …
MAX(B2:F2)8.5
MAX: Largest number.
- number1B2:F21 row × 5 cols: 7.5, 8, 7, 8.5, …
SUM(B2:F2)-MAX(B2:F2)30
The - operator subtracts.
MIN(B2:F2)7
MIN: Smallest number.
- number1B2:F21 row × 5 cols: 7.5, 8, 7, 8.5, …
SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2)23
The - operator subtracts.
COUNT(B2:F2)5
COUNT: Counts cells that contain numbers.
- value1B2:F21 row × 5 cols: 7.5, 8, 7, 8.5, …
COUNT(B2:F2)-23
The - operator subtracts.
SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-27.666666667
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=AVERAGE(LARGE(B2:F2,{2,3,4}))	7.67, 6.83, 8.33…	Averages the 2nd to 4th largest marks: the middle three of five.

Common mistakes

With fewer than three marks, COUNT-2 is 0 or less and the formula returns #DIV/0! or a wrong value.
Blank marks are left out of SUM, MAX, MIN and COUNT alike, so a missing judge doesn't break the formula, but it changes how many marks are averaged.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Average excluding the highest and lowest values

In G2, average the five judges' marks without the single highest and lowest, then fill down to G5.

Questions

Is there a built-in function for this?

TRIMMEAN drops a percentage of values from both ends: =TRIMMEAN(B2:F2,0.4) drops 40% of 5 values, one from each end.

How do I drop the two highest and two lowest?

Average the middle ranks. With seven judges in B2:H2: =AVERAGE(LARGE(B2:H2,{3,4,5})).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.