Buzz
This article introduces you to the AVERAGEIFS function - one of the commonly used statistical functions in Excel.
Description: Returns the average value of multiple criteria.
Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
In summary:
- average_range: The range of data to calculate the average, including numbers, names, or arrays containing numbers, is a required parameter.
- criteria_range1, criteria_range2: Ranges containing the conditions for calculating the sum, where criteria_range1 is a required parameter, and other criteria_range are optional, with a maximum of 127 arguments, are mandatory parameters.
- criteria1, criteria2, ...: The conditions for calculating the average, where criteria1 is a required parameter, and other criteria are optional parameters, containing a maximum of 127 conditions.
- If any of the arguments contain logical values or empty cells -> those values are ignored.
- If the calculation range is empty or contains text -> the function returns the error value #DIV/0.
- If a cell in the criteria is left blank -> the function treats it as a value equal to 0.
- If the value in the data cell is the logical value True -> it is considered as 1, and the value False is considered as 0.
- If the values in average_range cannot be converted to numbers -> the function returns the error value #DIV/0.
- If there are no values that meet all the conditions -> the function returns the error value #DIV/0!.
- Special characters such as *,? can be used as wildcards in the criteria for calculating the average.
For example:
Calculate the average of values with conditions in the following data table:
1. Calculate the average profit of consumer product groups with profits greater than 200.
- In the cell where you want to calculate, enter the formula: =AVERAGEIFS(C6:C10,B6:B10,'=*consumer*',C6:C10,'>200')
- Press Enter -> the average value of consumer products with profits greater than 200 is:
2. Calculate the average profit of consumer products excluding the Chinese product group.
- In the cell where you want to calculate, enter the formula: =AVERAGEIFS(C6:C10,B6:B10,'=*consumer*',B6:B10,'<>*China*')
- Press Enter -> the average profit value of consumer products excluding Chinese products is:
Above is the specific guide and example of using the AVERAGEIFS function in Excel.
Wishing you all success!
