In this Excel tutorial, we will learn how to calculate conditional average in Excel. We can use the AVERAGEIF or AVERAGEIFS function for this purpose. Conditional averages involve calculating the average of a subset of data that meets specific criteria. This is particularly useful when you want to analyze a portion of your dataset that satisfies certain conditions without including the entire dataset in your calculations.
How to Apply AVERAGEIF Function to Calculate Conditional Average in Excel
Here, we will show two examples of using the AVERAGEIF function to calculate conditional averages. We will use the following dataset here. It contains departments and the annual salaries of a few employees.
Example 1: Apply AVERAGEIF Function for Text Condition
Here, we will calculate the average salary of the employees who are from the Sales department. For that, apply the following formula in cell C19.
=AVERAGEIF(D6:D16,C18,E6:E16)
Example 2: Use AVERAGEIF Function for Numerical Condition
Here, we will calculate the average salary of employees with an annual salary of over $100000. Apply the following formula in cell C19.
=AVERAGEIF(E6:E16,">"&C18)
Read More: How to Calculate Average Only for Cells with Values in Excel
How to Use AVERAGEIFS Function to Calculate Average with Multiple Conditions
Here, we will calculate the average salary of the employees who are from the Sales department and have an annual salary over $100000. For that, We will use the AVERAGEIFS function. Apply the following formula in cell C20.
=AVERAGEIFS(E6:E16,D6:D16,C18,E6:E16,">"&C19)
Read More:Â How to Exclude a Cell in Excel AVERAGE Formula
This concludes our article on how to calculate conditional average in Excel. We used the AVERAGEIF and AVERAGEIFS functions for this purpose. We hope this article was helpful to you. Let us know your feedback.
Conditional Average Excel: Knowledge Hub
<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!