The Excel AVERAGEIF Function

Related Functions:
AVERAGEIFS
SUMIF
SUMIFS

Basic Description

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 :

AVERAGEIF( range, criteria, [average_range] )

Where the function arguments are:


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
[average_range] - An optional array of numeric values (or cells containing numbers) for which the average is to be calculated, if the corresponding range entry satisfies the supplied criteria
If the average_range argument is omitted, the values in the initial range argument are averaged

Wildcards

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:

- a numeric value (including integers, decimals, dates, times, and logical values) (eg. 10, 01/01/2008, TRUE)
or
- a text string (eg. "Text", "Thursday") - MUST be supplied in quotes
or
- an expression (eg. ">12", "<>0") - MUST be supplied in quotes

and can be entered into the function either directly, as a value returned from another function, or as a reference to a cell containing a value.

The Excel Averageif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be classed as equal.


Excel Averageif Function Examples

The following spreadsheet shows several examples of the Excel Averageif function.

The spreadsheet on the left shows the formulas used and the spreadsheet on the right shows the results.

 Formulas:
  A B C
1 Monday 500  
2 Tuesday 50  
3 Thursday 100  
4 Friday 100  
5 Thursday 200  
6 5 300  
7 2 200  
8 3 100  
9 4 50  
10 5 100  
11 1 50  
12 TRUE 200  
13 TRUE 250  
14 FALSE 50  
15      
16 =AVERAGEIF( A1:A14, "Thursday", B1:B14 )
17 =AVERAGEIF( A1:A14, 5, B1:B14 )
18 =AVERAGEIF( A1:A14, ">2", B1:B14 )
19 =AVERAGEIF( A1:A14, TRUE, B1:B14 )
20 =AVERAGEIF( A1:A14, "<>TRUE", B1:B14 )
 Results:
  A B
1 Monday 500
2 Tuesday 50
3 Thursday 100
4 Friday 100
5 Thursday 200
6 5 300
7 2 200
8 3 100
9 4 50
10 5 100
11 1 50
12 TRUE 200
13 TRUE 250
14 FALSE 50
15    
16 150  
17 200  
18 137.5  
19 225  
20 150  

In the above spreadsheet, each call to the Excel Averageif function tests the contents of cells A1 to A14 against the supplied criteria. For of the cells that satisfy the criteria, the corresponding cells in the range B1 to B14 are included in the average calculation.

In cell A16, for example, the cells A3 and A5 are both equal to the text string "Thursday". Therefore, the average is calculated for the corresponding values from cells B3 and B5 (100 and 200), resulting in an average value of 150.


Further examples of the Excel Averageif function can be found on the Microsoft Office website.


Averageif Function Errors

The most common errors from the Excel Averageif function are listed in the table below :

Common Errors
#DIV/0! - Occurs if either:
none of the values in the supplied range array satisfy the supplied criteria
or
the values to be averaged are all non-numeric
#VALUE! - Occurs if the criteria argument is a text string with length greater than 255 characters.
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net