The Excel AVERAGEIF function finds values in a supplied array, that satisfy a given criteria, and returns the average (ie. the statistical mean) of the corresponding values in a second supplied array.
The function is new in Excel 2007, and so is not available in earlier versions of Excel.
The syntax of the Averageif function is :
|range||-||An array of values (or range of cells containing values) to be tested against the given criteria|
|criteria||-||The condition to be tested against each of the values in range|
You can also use the following wildcards in text-related criteria:
? - matches any single character
* - matches any sequence of characters
if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".
The supplied criteria can be either:or or
The Excel Averageif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be classed as equal.
The following spreadsheet shows several examples of the Excel Averageif function.
In the above spreadsheet, each call to the Excel Averageif function tests the contents of cells A1 to A14 against the supplied criteria. The average calculation uses the corresponding cells in the range B1 to B14.
The formula in cell A16, for example, requires the average of cells in the range B1 - B14, for which the corresponding values in column A are equal to the value "Thursday". As cells A3 and A5 are both equal to the text string "Thursday", the average is calculated for the values in cells B3 and B5.
Further examples of the Excel Averageif function can be found on the Microsoft Office website.
The most common errors from the Excel Averageif function are listed in the table below :
|#DIV/0!||-||Occurs if either:or|
|#VALUE!||-||Occurs if the criteria argument is a text string with length greater than 255 characters.|