Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.

In the image below we have 10 scores.

The average is 73.5.

The formula to calculate the Olympic Average is.

=AVERAGE(DROP(DROP(SORT(A2:A11),-1),1))

To understand this formula, we need to start on the inside and work our way to the outside.

The SORT function sorts the numbers in the range from low to high.

The DROP function takes that sorted list and “drops” the highest (last) value using the -1 argument.

The -1 means drop the last entry, in this case the largest value.

The next DROP uses the 1 argument and drops the first (lowest) value.

These two DROP functions together remove the highest and lowest values.

This amended range is what the AVERAGE function uses for its calculation.

The result is a slightly higher average because the 5 was dragging the average down.