When analysing data you may want to check for outliers. You can use MIN and MAX to get minimum and maximum values but you may want to average a certain  number of top or bottom numbers. Here’s how you can do it.

In the image below column A has 100 random numbers between 100 and 500. You can download the example file at the bottom of the post.

The yellow cell D1 is where we can specify how many top or bottom numbers to average.

The formulas use AVERAGE, LARGE, SMALL and SEQUENCE. The functions are explained below.

The LARGE function is like the MAX function but is flexible. The LARGE function can find the second, third or fourth highest value in a range. The formula below finds the fifth highest value in our range in column A.

=LARGE(A2:A101,5)

The SMALL function is like LARGE but works on the lowest numbers. The formula finds the fifth lowest number in our range.

=SMALL(A2:A101,5)

The examples that follow all use the LARGE function but also apply to the SMALL function.

The LARGE function can accept an array as the second argument. An array is a list of values. Arrays use the curly brackets (called braces) around the list. The following formula returns a list of the top five numbers from our list.

=LARGE(A2:A101,{1;2;3;4;5})

The SEQUENCE function returns sequential numbers and can be used instead of a keyed-in array as the second argument of the LARGE function. The formula below also returns the highest five numbers from our list.

=LARGE(A2:A101,SEQUENCE(5))

The value in the SEQUENCE function can be linked to a cell value to make it flexible. In our example this also returns the highest five numbers from our list.

=LARGE(A2:A101,SEQUENCE(D1))

The LARGE and SEQUENCE function combination can then be used inside the AVERAGE function to average the top five values.

=AVERAGE(LARGE(A2:A101,SEQUENCE(D1)))

Changing cell D1 will update how many top numbers are averaged.

I checked the results using the SORT function and manually using AVERAGE functions. You can see the images below.

You can download the example file at the button below.