AVERAGEIF Function

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.

Syntax: 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.

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

Averageif Function Example-1

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

 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. 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.

Averageif Function Example-2

Formulas:

Average_formula

Results:

Average_result

 

Averageif Function Example-3

Formulas:

averageif_formula

Results:

Averageif_result

 

Averageif Function Errors

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

#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.

 

Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment