Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

I recently posted this example of an array constant to avoid a lookup table for VLOOKUP.

Today, an array constant is employed to collect — and then calculate the average of — the four numbers that are the highest, 2nd highest, 3rd highest, and 4th highest test scores in a list.

In the picture, students have taken 7 tests, and the averages of their 4 highest test scores are calculated by this formula in cell I5, copied down to cell I16:
=CEILING(AVERAGE(LARGE(B5:H5,{1,2,3,4})),1)

The CEILING function rounds up a decimalized average test score to its next whole number.
To average the 4 lowest numbers, substitute the LARGE function with the SMALL function.

I used 4 in this blog example as the count of test scores to be averaged. To average the 3 highest or lowest numbers, change the array constant elements to {1,2,3}. To average the 6 highest or lowest scores, change the array constant elements to {1,2,3,4,5,6}, and so on.