AVERAGEIF Function in Excel

AVERAGEIF Function in Excel finds and returns the average of array that meets the specific condition. The AVERAGEIF function in Excel supports logical operators (>,<,<>,=) and wildcards (*,?) for pattern matching

Wild cards in AVERAGEIF Function

  • ? –    matches any single character
  • * –    matches any sequence of characters

Example:

The condition “A?e” will match all cells containing a text string beginning with “A” and ending in “e” and only one character in between i.e. A_e.

The condition “A*e” will match all cells containing a text string beginning with “A” and ending in “e” with multiple characters(n) in between i.e. A_______e.

 

Syntax of AVERAGEIF Function in Excel:

AVERAGEIF( range, criteria, [average_range])
  • range – An Array of range to be tested against the supplied criteria.
  • criteria – The criteria or condition on which average has to be calculated.
  • [average_range] – An optional array of numeric values for which the average is to be calculated.

Note: If the [average_range] argument is omitted, the average is calculated for values in the range argument

 

Example of AVERAGEIF Function in Excel

Formula

AVERAGE FUNCTION IN EXCEL 1

  • First three examples states the use of logical operation in AVERAGEIF function.
  • Last two examples states the use of wildcards in AVERAGEIF function.

Note : in the First Example, the [average_range] argument is omitted, the average is calculated for values in the range argument

So the result will be like

Result

AVERAGE FUNCTION IN EXCEL 2

If you want to calculate the average based on multiple conditions then use AVERAGEIFS Function in Excel, which is discussed in the next chapter.

previous AVERAGEIF Function in Excel                                                                                                                next AVERAGEIF Function in Excel