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.