Conditional Average in Excel (Complete Guide)

Conditional averages involve calculating the average of a subset of data that meets specific criteria. In this Excel tutorial, we will demonstrate how to calculate a conditional average in Excel using the AVERAGEIF function and the AVERAGEIFS FUNCTION.

We will present three examples using these functions to calculate conditional averages using the following dataset containing the annual salaries of some employees in different departments.

Example 1 – Applying the AVERAGEIF Function for a Text Condition

Let’s calculate the average salary of the employees in the Sales department.

Apply the following formula in cell C19:

=AVERAGEIF(D6:D16,C18,E6:E16)

Example 2 – Using the AVERAGEIF Function with a Numerical Condition

Now let’s calculate the average salary of the employees with an annual salary of over $100000.

Apply the following formula in cell C19:

=AVERAGEIF(E6:E16,">"&C18)

Example 3 – Using the AVERAGEIFS Function to Calculate the Average with Multiple Conditions

Let’s calculate the average salary of the employees who are from the Sales department and have an annual salary over $100000. For conditional averages with multiple criteria, we use the AVERAGEIFS function.

Apply the following formula in cell C20:

=AVERAGEIFS(E6:E16,D6:D16,C18,E6:E16,">"&C19)

Conditional Average in Excel: Knowledge Hub

<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

samwel gurt Jul 20, 2024 at 2:10 PM

loved it.
- Reply
  Shamima Sultana Jul 24, 2024 at 9:28 PM
  
  Hello Samwel Gurt,
  
  Thank you so much. We are glad to hear that you loved our article. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.
  
  Regards
  ExcelDemy
Alex Aug 7, 2024 at 10:04 AM

Thanks! This is very helpful to me.
One more question is, how can I get color from a specific cell in another sheet?
- Reply
  Shamima Sultana Aug 7, 2024 at 1:46 PM
  Hello Alex,
  
  You are most welcome. Thanks for your appreciation, it’s great to hear that you found the article helpful.
  To get cell color from a specific cell you can use the user defined function.
  Copy paste the VBA code:
```
Function GetCellColor(rng As Range) As Long
    GetCellColor = rng.Interior.Color
End Function
```
  Use this function in a cell to reference the specific cell from another sheet whose color you want to retrieve.
  
  You also can follow this article: How to Get Cell Color in Excel
  
  Regards
  ExcelDemy

Conditional Average in Excel (Complete Guide)

Example 1 – Applying the AVERAGEIF Function for a Text Condition

Example 2 – Using the AVERAGEIF Function with a Numerical Condition

Example 3 – Using the AVERAGEIFS Function to Calculate the Average with Multiple Conditions

Conditional Average in Excel: Knowledge Hub

Leave a reply Cancel reply