How to Use the AVERAGEIF Function in Excel

This guide explains how to use AVERAGEIF Function in Excel, including its syntax, required arguments, practical examples, and common errors.

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!