Averaging the Average Test Score of Each Student
In this example, we have five students, and each student has taken five math tests. As you can see from the following table, Column A contains the student name, and Columns B, C, D, E, and F contain the corresponding test scores.
Let's say that you'd like to calculate an average from the average test score of each student, but that you'd like to exclude the lowest and highest scores from each student's average.
In other words, for each student, we take their total score, subtract their lowest and highest scores, and divide by three to get their average. Then we use these individual averages to calculate the overall student average.
For this calculation, you can use the following formula that needs to be confirmed with just ENTER...
=AVERAGE(MMULT(SUBTOTAL({4,5,9},OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),0,1)),{-1;-1;1})/3)
Based on the sample data, the formula returns 76.53, rounded to two decimal places.