# 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. ###### 2 comments on “Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List”
1. kiran says:

in this process i have only 6 students are there
i have write the formula this type
=CEILING(AVERAGE(LARGE(B5:H5,{1,2,3,4,5,6,7})),1)

then error is coming. how to reduce this error

• Orlando says:

Remove the 7 from your formula.