Excel AVERAGEIF (Table of Contents)
AVERAGEIF in Excel
AVERAGEIF function in Excel is used to calculate the average of numbers on the basis of defined criteria. For example, we have sales data of 4 product and we want to find out the average sale of any product from the entire data or selected portion of data. For that, we use AVERAGEIF whereas per syntax first select the range of all the products and then give select the criteria by selecting the product name for which we want to find average sale and then select the complete range of sold quantities.
Now, we have known the exact AVERAGEIF function, we will now learn how to write a formula using the function and the syntax of the formula.
The syntax of the AVERAGEIF function
Here is the explanation of all the elements of the syntax.
Range – A range of cells on which the criteria or condition is to be applied. The range can include a number, cell references and names as well. In case there is no “Average-range” argument, this range will be used for the calculation of average.
Criteria – Is a condition on the basis of which, cells will be averaged. It could be a number, cell reference, text value, logical statement (like “<5”) or expression.
average_range – The cell range to average. It is optional. In the absence of any average_range, range is used to calculate the average.
How to use AVERAGEIF Function in Excel?
Now, we will learn how to use the AVERAGEIF function in Excel with the help of various examples. It is an inbuilt function in Excel.
AVERAGEIF in Excel – Example #1
The AVERAGEIF function in Excel calculates the average of cells that exactly match the criteria or the condition specified.
Below you will see a table containing the product name and its units that are sold.
In this example, we need to find the average units sold for product B.
We can directly type the formula by starting it with = and typing AVERAGEIF or we can also select the function from the ribbon as shown below:
Now Function Arguments Box will appear. Then select Range: A2:A13, Criteria: B15, Average_range: B2:B13
Press OK. We get the below result which is the average units sold for product B.
We can also type in “B” instead of cell reference in the formula as shown below in the cell B15:
Explanation of the example :
Range: We have taken A2: A13 as range, on which our criteria (B) will be applied.
Criteria: Since we wanted to know the average units sold for B so B is our criteria.
Average _range: B2: B13 is the range of cells from which Excel will do the average.
Taking our last example. If we want to know the average units sold for D product.
We will get the average units sold for D product = 194.33
AVERAGEIF in Excel – Example #2
We have a sample data table that contains the score of students for the second and third semester.
Now if we want to calculate the average second-semester score from the above data, we can do it by writing the formula as shown below:
We will write B2:B7 as range and will make it absolute by pressing F4, then we will write “second” as criteria and C2:C7 as average_range and will also make it an absolute range. Now, we will get the average third-semester score by using cell reference B6 instead of the word “Third” as criteria.
And we will press the Enter key to get the result.
AVERAGEIF in Excel – Example #3
In this sample data, we have sales data for cities of different zones.
Now if we want to find the average sale of a particular zone say, for instance, West. We can get average sales as below:
Press the Enter key to get the result.
So these examples must have given you a fair understanding of the use of AVERAGEIF function.
AVERAGEIF in Excel – Example #4
AVERAGEIF using a wildcard in text criteria
We use a wildcard in AVERAGEIF function when the text criteria is partially met or is preceded or followed by any other word.
Below is a sample sales data of confectionary items. If we want to know the average sales for all cakes instead of a specific cake, we then use a wildcard(*). Like in our case, the keyword cake is preceded by other words like chocolate, fruit, plain etc. We can overcome this limitation by using asterisk sign before the word “cake” to fetch the sales data of all sorts of cakes.
In the same way, we can add an asterisk sign after the search keyword if it is followed by some other word.
So we will write the formula as below and will add * sign before our criteria. In this case, our criteria is cakes.
After pressing the Enter key, we see the result.
If the criteria or the keyword is followed by any other word just add an asterisk sign after the keyword /criteria. Like “cake*”
Likewise, if you want to average the sales of all other items apart from cakes, you can use the below formula using a wildcard. Which is “<>*cake”.
Press the Enter key to get the result.
AVERAGEIF in Excel – Example #5
AVERAGEIF using logical operations or numeric criteria
Many a times we have to average cells based on logical statements like “>100” or “<100”. Or as per numeric criteria. How we will do this?
We will understand this with the help of an example.
Taking our sample data. If we want to know the average of units sold above 250. We will find this out as below:
After pressing the Enter key, we will get a result as shown below.
So the average of units sold above 250 belongs to products E and D. Similarly, if we want to know the average units sold below 100. We can find this as shown below:
After pressing the Enter key will give us the result.
This is how we can use AVERAGEIF with logical statements as criteria.
Things to Remember
- If the criteria for calculating the average of numbers is text or logical expression, they should always be written in double quotes.
- In case no cell in the range meets the criteria for average or range is empty, the function will return #DIV0! Error.
- If the cell as criteria is blank, the function will take it as a 0.
- Excel treats the default “criteria” operand as equals to, other operands may include >,<,<>, >=and <= as well.
- If any of the cells in a range has True or False. The function will ignore it.
Recommended Articles
This has been a guide to AVERAGEIF in Excel. Here we discuss how to calculate Average using AVERAGEIF Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles–
