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

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

Remove the 7 from your formula.