The following article introduces you to the AVERAGEIF function - one of the functions in the statistical function group is very popular in Excel.
Description: The function returns the average of the arguments given the specified condition.
Syntax: AVERAGEIF (range, criteria, [average_range])
Inside:
- range: The data area to calculate the average value, is a required parameter.
- criteria: The condition to calculate the average for the arguments, which is a required parameter.
- average_range: The set of actual cells counted, which is an optional value, if omitted the range used.
Attention:
- If the value of the argument contains a logical value or a blank cell -> that value is ignored.
- If the range is blank or text -> the function returns the # DIV / 0 error value
- If a cell in the criterion is blank -> the function considers it as a value of 0.
- Alternative characters such as *,? . may be used under average conditions.
- average_range does not necessarily have to be the same size as the range.
For example:
Calculate the average of the values with the condition in the following data table:
1. Calculate the average of the profits of the commodity groups with profits greater than 200.
- In the cell to calculate enter the formula: = AVERAGEIF (C6: C10, "> 200", C6: C10)
- Press Enter -> the average of items with profits greater than 200 is:
2. Calculate the average plus profit of consumer goods.
- In the cell to calculate enter the formula: = AVERAGEIF (B6: B10, "= * consumption *", C6: C10)
- Press Enter -> average profit of consumer goods items is:
3. Calculate the average plus profit of the items except consumer goods.
- In the cell to calculate, enter the formula: = AVERAGEIF (B6: B10, "<> * consumption *", C6: C10) -> press Enter -> average plus profit of commodity groups except consumer goods was:
Above are instructions and specific examples when using the AVERAGEIF function in Excel.
Good luck!