AVERAGE function: To calculate the average of  a range of cells in MS Excel, we can use the Average function.

So, the formula to get the Average of  E4 to E9 columns values are '=AVERAGE(E4:E9)'. 

Now, we can see the average result of all 6 students as follows.

AVERAGEIF function: AVERAGEIF function is used to calculate averages with only one criteria. In the below example we want to calculate the average result of only female students. So, We use AVERAGEIF function with criteria of “F”(Female students).

So, the formula to calculate the Average of  C4 to C9 columns values with criteria “F” on the column E4 to E9 is' =AVERAGEIF(C4:C9,”F”,E4:E9)'. 

And the result is below.

AVERAGEIFS function

AVERAGEIFS function is used to calculate average of  cell value with multiple criteria.

So in the below example, we want to calculate the average of Male students who are 13 years old. So in this situation, we have two criteria under the sex column “M”(Male) and under the Age column, the criteria are “13”. The value of the E column only if the C column value is "M" and the D column value is "13".

 So our formula will be '=AVERAGEIFS(E4:E9, C4:C9, "M", D4:D9, 13)' .

Here

·         E4 to E9 is the average range

·         C4 to C9 is criteria 1 range

·         “M” is the criteria value for C4 to C9

·         D4 to D9 is criteria 2 range

·         13 is criteria value of D4 to D9

So, the result is below.