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.
|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
Note: The AVERAGEIF function is not case-sensitive. So, for example, the text strings “TEXT” and “text” will be classed as equal.
|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|
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:
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.