To find the average of only those values that meet a specific criterion, you need to employ the AVERAGEIF function in Excel.
Learn all about this function in the article that follows.
Table of Contents
AVERAGEIF Function
The AVERAGEIF function of Excel is a statistical function that allows users to average only those cell values that meet a specified criterion. The specified criteria can include text, logical operators like >, <, =, < > and wildcard characters too.
Syntax
Syntax of the AVERAGEIF function is stated as follows.
= AVERAGEIF (range, criteria, average_range)
Arguments
Let’s break down the above syntax to see how each of its arguments function.
- Range – The criterion specified for averaging specific values is looked up from this range. This can be a single or more cell, any reference, or value.
- Criteria – This includes the criterion based on which specific values are to be averaged.
- Average_range – This defines the range containing values that are to be averaged. If left vacant, Excel automatically assumes this argument equal to the range (first argument).
Return Value
The AVERAGEIF returns the average of those values from the range that meet the specified criterion.
Functions Library
You may access the AVERAGEIF function from the Functions Library in Excel as follows.
Formulas > Functions Library > More Functions > Statistical Functions > AVERAGEIF
Examples of the Function
It’s time to delve into some examples to learn how the AVERAGEIF function practically works and how can you use it to your benefit.
1. Simple operation of the AVERAGEIF function
Below is a screenshot that manifests sales of different regions for different years.
To find the average sales in Toronto over the given years, here’s what needs to be done.
Step 1:
Activate the cell where you want the average value to be populated and compose the AVERAGEIF function as follows.
= AVERAGEIF (C2:C11, “Toronto”, “B2:B11”)
- The range is defined as C2:C11, where the criterion is to be looked up for as this column contains the region information.
- The criterion i.e. Toronto is enclosed in double quotation marks “Toronto”. This is because it is a text value.
- The average_range is specified as B2:B11, where the values to be averaged are populated.
Step 2:
Hit enter to see the following results.
Excel has calculated the average of all those sales whereby the corresponding region is Toronto.
2. Using AVERAGEIF function with logical operators
Sometimes the criteria that you specify is not only a single value but a condition. For example, average all numbers greater than 3, etc.
You can apply the AVERAGEIF function to find similar averages by employing logical operators. For a quick reiteration, logical operators that can be used in Excel are as follows.
Logical Operator | Meaning |
= | Equal Sign |
> | Greater than |
< | Less than |
<> | Not equal to |
>= | Greater than or equal to |
<= | Lesser than or equal to |
An example can help us learn better how logical operators may be used in pair with the AVERAGEIF function.
Step 1:
Continuing the same example, as above, if we want to calculate all those sales that are equal to or greater than $5000, compose the AVERAGEIF function as follows.
= AVERAGEIF (B2:B12, “>=5000”)
- The range is set as B2:B12 as the values are to be looked up are populated under column B.
- The criterion is set to “>=5000”. The Logical operator >= signifies greater than or equal to 5000. It is important to enclose the criterion in double quotation marks for Excel to recognize it as text.
- We want Excel to calculate the average of sales based on a specific sale value. As the range and average_range in the foregoing example are the same, the last argument can be omitted. Excel will assume the average_range to be the range.
Step 2:
Once the formula is devised, hit enter to see the following results.
Excel has calculated the average of all those sales that are equal to or greater than $5000.
3. Using AVERAGEIF function with wildcard characters
Wildcard characters and their appropriate usage can help you straighten out many things you thought you’d keep laboring about.
Wildcard Characters | Usage |
* | Equates to any number of characters in any order |
? | Equates to any single character in the same order |
Take a look at the example below to better decipher the application of wildcard characters.
The image below represents Sales made to different customers.
A deeper look into the data tells that in many instances, the name of the same customer is spelled differently. For example, Sania can be seen spelled as, Sanea, and Sanya, when she is the same customer.
If we want to find the average sales per customer, how can we define a criterion that meets all these spelling variants?
Step 1:
Wildcard characters can be used to do so. Compose the average formula as follows.
= AVERAGEIF (A2:A11, “San?a”, B2:B11)
- The range is set to A2:A11, where the customer name is to be looked up for.
- The criterion is set to “San?a” as all the three spelling variants involve a different 4th letter. Substituting the fourth letter with a wildcard character (?), Excel would consider all spelling variants irrespective of the fourth character.
- The average_range is set to B2:B11 where the sales are populated.
Step 2:
Hit enter to see the following results.
Excel has calculated the average of cells containing all spellings of Sania with a varying fourth character.
Troubleshooting the AVERAGEIF Function
The AVERAGEIF function is one of the most basic functions of Excel and is likely not to trouble you unduly. However, keeping in mind the following pointers can help you get rid of any problems that users commonly encounter with the AVERAGEIF function.
- Empty Cells – The AVERAGEIF function ignores those cells that are vacant. These cells are not included in the computation of average.
- #DIV/0! Error – Excel will give back the #DIV/0! Error when none of the cells meet the criteria specified by you. This technically means that no value is to be averaged.
- Range Compatibility – The argument ‘range’ and ‘average_range’ in Excel necessarily need not be the same in size.
Conclusion:
The AVERAGEIF function can help you get through so many of your routine tasks in Excel. A little practice of the said function can help you master the same.
Suggested Tutorial: How to Do Factorial in Excel?