Excel AVERAGEIF Function (Example + Video)
When to use Excel AVERAGEIF Function
Excel AVERAGEIF function can be used when you want to get the average (arithmetic mean) of all the values in a range of cells that meet a given criteria.
What it Returns
It returns a numerical value that represents the average (arithmetic mean) of the values in a range of cells that meets the given criteria.
Syntax
=AVERAGEIF(range, criteria, [average_range])
Input Arguments
- range – the range of cells against which the criteria is evaluated. It could be numbers, text, arrays, or references that contain numbers.
- criteria – the criterion that is checked against the range and determines which cells to average.
- [average_range] – (optional) the cells to add. If this argument is omitted, it uses range as the sum_range.
Additional Notes
- Empty cells are ignored in average_range.
- If the criteria is an empty cell, Excel treats it as 0.
- If no cell meets the criteria, a #DIV/0! error is returned.
- If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
- Criteria could be a number, expression, cell reference, text, or a formula.
- Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes.
- Wildcard characters can be used in criteria.
Excel AVERAGEIF Function – Examples
Here are three examples of using the Excel AVERAGEIF function.
#1 Getting the Average of a Matching Criteria
In the above example, Excel AverageIf functions checks for the criteria “Tom” in A2:A6. For all the matching values in Column A, it gives the average of corresponding values in column B.
In this example, all the values in column B for Tom are averaged (10, 12, and 0).
You can also cell reference instead of manually entering the criteria.
#2 Using Wildcard Characters in Excel AVERAGEIF Function
You can use wildcard characters in the Excel AVERAGEIF function to construct criteria.
There are three wildcard characters in Excel – a question mark (?), an asterisk (*), and a tilde (~).
- A question mark (?) matches any single character.
- An asterisk (*) matches any sequence of characters.
- If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
In the above example, the AVERAGEIF function checks for the criteria where the name has the alphabet ‘a’ in it. Since it is flanked by asterisks (*) at both sides, it means that the word can have any number of characters before and after the alphabet ‘a’.
Of all the names, Jane and Arjun meet the criteria and the function gives the average of values for these two names (12 and 15).
#3 Using Comparison Operators within Excel AVERAGEIF function
In the Excel AVERAGEIF function, if you omit the last argument (average_range) then it takes the first argument (the criteria range) as the average_range also. In the above example,
In the above example, B2:B6 is used to check for the criteria as well as to calculate the average. The criteria here is “>10” which checks for all the numbers greater than 10, and averages them.
Note that the operator always needs to be in double quotes. So you can use “>10” or “>”&10 or “>”&B2 (where B2 has the numerical value).
Excel AVERAGEIF Function – Video Tutorial
Related Excel Functions:
- Excel AVERAGE Function.
- Excel AVERAGEIFS Function.
- Excel SUM Function.
- Excel SUMIF Function.
- Excel SUMIFS Function.
- Excel SUMPRODUCT Function
Other articles you may also like:
