Buzz
The AVERAGEIFS function calculates the average with multiple specified conditions. It is an advanced function with superior features compared to the regular AVERAGE function. In today's article, Mytour will introduce the formula and how to use this function through some illustrated examples.
Guide on using AVERAGEIFS function in Excel - Illustrated examples
Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2…)
Where:
- Average_Range: Cells to calculate the average value
- Criteria_range1, criteria_range2, … : Areas to specify corresponding conditions
- Conditions1, conditions2,…: Specified conditions for each criteria_range above
Note:
- If Average_range is empty or contains text, the function returns an error #DIV0!. Cells with True are treated as the number 1, and cells with False are treated as the number 0.
- Cells in Criteria_range that are empty default to 0
- If no cell meets all conditions, the function also returns an error #DIV0!
In the example below, we can see how to use the AVERAGEIFS function in calculating the average value with multiple conditions.
Example 1: Calculate the average salary of employees with salaries greater than 3,000,000 but less than 7,000,000.
- Enter the formula as follows: =AVERAGEIFS(G5:G13, G5:G13, '>3000000', G5:G13, '<7000000')
- The average salary returned by the AVERAGEIFS function is 4,500,000
Example 2: Calculate the average salary of male employees with an Allowance of 500,000
- Enter the formula as follows: =AVERAGEIFS(G5:G13,H5:H13,'500000',D5:D13,'male')
- The average salary returned by the AVERAGEIFS function is 3,666,667
Example 3: Calculate the average salary of employees with salaries greater than 9,000,000
- Enter the formula as follows: =AVERAGEIFS(G5:G13,G5:G13,'>9000000')
- The average salary returned by the AVERAGEIFS function is #DIV/0!
In this article, we introduced the syntax and usage of the AVERAGEIFS function. With this, you can easily apply this function to calculate the average value with multiple specified conditions. AVERAGEIFS is an advanced development derived from the AVERAGEIF function - The function calculates the average with conditions.
