# How to average excluding the highest and lowest values in Excel

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)`.

**Excel:** `=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Diver | Judge 1 | Judge 2 | Judge 3 | Judge 4 | Judge 5 | Score |
| Ana | 7.5 | 8 | 7 | 8.5 | 7.5 |  |
| Bea | 6.5 | 7 | 9 | 7 | 6.5 |  |
| Cy | 8 | 8.5 | 8.5 | 9 | 8 |  |
| Dee | 5.5 | 6 | 6 | 6.5 | 4 |  |

Formula in G2, filled to G5: `=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)`

Result: 7.67; 6.83; 8.33; 5.83

## 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.

## Other ways

- `=AVERAGE(LARGE(B2:F2,{2,3,4}))`: 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.

## 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}))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/average-excluding-highest-and-lowest