To calculate the average of the data in Excel table, we will use the AVERAGE function, the familiar average function. However, almost calculating the average value in Excel comes with many different conditions. And so, you need the AVERAGEIF function to calculate conditional averages.
Syntax: = AVERAGEIF(range, criteria, [average_range])
The AVERAGEIF function syntax has the following arguments:
- Range (Required) One or more cells to average, including numbers or names, arrays, or references that contain numbers.
- Criteria (Required): The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
- Average_range (Optional): The actual set of cells to average. If omitted, range is used.
Example: We will proceed to calculate the average score in the data table below.
1.1 Calculate the average score of students with scores greater than 80.
Syntax: =AVERAGEIF(C2:C9,”>80″)
Result:
1.2 Calculate the average score of students with scores less than 80.
Syntax: =AVERAGEIF(C2:C9,”<80″)
Result:
1.3 Calculate the average score of male students who are male
Syntax: =AVERAGEIF(B2:B9,”Male”,C2:C9)
Result:
I wish you successful implementation!