You can use the AVERAGEIF function in Excel to calculate a conditional mean.
This function uses the following basic syntax:
=AVERAGEIF(A2:A7, "some value", B2:B7)
This particular formula calculates the average value in the range B2:B7 where the corresponding value in the range A2:A7 is equal to “some value.”
The following examples show how to use this function to calculate a conditional mean using the following dataset in Excel:
Example 1: Calculate Conditional Mean for Categorical Data
We can use the following formula to calculate the mean of the Points column only for the rows where the Team column has a value of “A.”
=AVERAGEIF(A2:A7, "A", B2:B7)
The following screenshot shows how to use this formula in practice:
The mean value in the Points column for the rows where Team is equal to “A” is 94.
We can manually verify this is correct by calculating the average of the points values for only the rows where Team is equal to “A”:
- Average of Points: (99 + 90 + 93) / 3 = 94
This matches the value calculated by the AVERAGEIF function.
Example 2: Calculate Conditional Mean for Numeric Data
We can use the following formula to calculate the mean of the Assists column only for the rows where the Points column has a value greater than or equal to 90:
=AVERAGEIF(B2:B7, ">=90", C2:C7)
The following screenshot shows how to use this formula in practice:
The mean value in the Assists column for the rows where Points is greater than or equal to 90 is 30.66667.
We can manually verify this is correct by calculating the average of the assists values for only the rows where points is greater than or equal to 90:
- Average of Assists: (33 + 28 + 31) / 3 = 30.66667
This matches the value calculated by the AVERAGEIF function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find Mean, Median & Mode in Excel
How to Calculate Standard Error of the Mean in Excel
How to Calculate Mean Squared Error (MSE) in Excel