The AVERAGEIF function returns the average, arithmetic mean, of all the cells in a range that meet a given criteria.

  • This function was introduced in Excel 2007 and so is not available in earlier versions.

Syntax

=AVERAGEIF(range,criteria,[average_range])

Arguments

Argument Description
range An array of values, or range of cells containing values, to be tested against the supplied criteria
criteria The condition to be tested against each of the values in range
[average_range] Optional. An array of numeric values, or cells containing numbers, for which the average is to be calculated, if the corresponding range value satisfies the supplied criteria

  If the [average_range] argument is omitted, the average is calculated for values in the initial supplied range

Note: The AVERAGEIF function is not case-sensitive. So, for example, the text strings “TEXT” and “text” will be classed as equal.

Examples

  A B C D E F
1 Sale Value Commission   Formula Result Notes
2 $100,500 $7,035   =AVERAGEIF(B2:B5,”<15000″) 10,523.33 Average of all commissions less than 15,000
3 $210,500 $14,735   =AVERAGEIF(A2:A5,”>150000″,B2:B5) 8,417.50 Average of all commissions with a property value greater than 550000
4 $140,000 $9,800        
5 $300,000 $21,000        

Usage note: You can also use the following wildcards in text matches for the criteria argument:

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

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

  • If you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the criteria argument is a text string with length greater than 255 characters
#DIV/0! Occurs if either:

  none of the values in the supplied range array satisfy the supplied criteria
  the values to be averaged are all non-numeric

The AVERAGE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

  • Average – the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers.
  • Median – the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.
  • Mode – the most frequently occurring number in a group of numbers.