AverageIF in excel calculates the average of the numbers just like the average function in excel but the only difference is that AverageIF is a conditional function and it calculates the average only when the criteria given to the function is met, it takes three required arguments, range with followed by the criteria and then average range.
AverageIF Excel Function (Table of Contents)
Excel AverageIF Function
We have numerous excel functions to work in different situations. Average function is used to calculate the average of the range cell values. Similarly, we can take out the average of specific values based on other columns as well. Based on the mentioned criteria we can calculate the average of the numbers by using AVERAGEIF function in excel. In this article, we will take you through the function in excel i.e. AVERAGEIF.
What Does AVERAGEIF Function Do in Excel?
AVERAGEIF function calculates the average of specified numbers but also based on supplied criteria.
For an example look at the below data sample.
From this data table, we can easily tell what the average sales are by applying the AVERAGE function.
So, the average sale is 180 from the above data table. This is easy but how do you tell what is the average of the product “A” only??
This is where the AVERAGEIF function will help us. Here criteria to calculate the average is “Product A”.
Before we practically apply AVERAGEIF function let me explain you the syntax of AVERAGEIF function. AVERAGEIF function has 3 arguments to deal with, below are the criteria’s.
- Range: The range of cells that has criteria values. In our above example, our range was “Product”.
- Criteria: From the Range for which item we need to take the average out i.e. Criteria. For an example in the above table out of all the products for which product we need to take the average of.
- [Average Range]: For this, we need to select for number column that we want to calculate the average of.
How to use AverageIF Function in Excel?
Below are the examples of AverageIF Function in excel.
Let’s take the same data for an example.
From this sales table, we need to find the average sales of the product “A”. Follow the below steps to calculate average.
Step 1: Open AVERAGEIF excle function in one of the cells.
Step 2: Select the range as product list i.e. from A2 to A10.
Step 3: So, now out of a selected range of products for which product we need to find the average. In this case, we need to find the average for the product “A”.
Step 4: Next up for which numbers we need to find the average. We need to find the average for sales column.
Step 5: Close the bracket and hit enter we will get the average sale for the product “A”.
So, the AVERAGEIF excel function calculates the average only for the product “A” is 200.33 whereas the overall average is just 180.
Now, look at the below data. In this data, we have a product name, product price, and a number of units sold.
So, from the above list, we need to calculate the average unit sold when the unit price is >=25. So, for this, we need to use the operator key greater than (>) in the criteria argument. Follow the below steps to calculate average.
Step 1: Open AVERAGEIF excel function in one of the cells.
Step 2: Select RANGE as Unit Price column from B2 to B10.
Step 3: Since we need to take the average only for the unit price >=25 enter the criteria as “>=25”.
Step 4: Average Range is Unit Sold Column from C2 to C10.
Step 5: We are done with the formula. Close bracket and hit enter key to get the average units sold number when the unit price is >=25.
So, when the unit price is >=25 average sales is 2,221 units. Similarly, now we will calculate the average sales when the unit price is <25.
So, when the price drops less than 25 per unit average sale is only 1,917.
Wildcard characters are such useful in excel functions. One of the problems with AVERAGE or AVERAGEIF excel function is it takes zero as one value and calculates the average value including zero. Now for an example look at the below data.
In the above image, the AVERAGE function calculates the average of the numbers from B2 to B6. From B2 to B6 we have five numbers but we have zero as well. So when we calculate the average excluding zero we get the increased average.
So we got the average as 7 now. But in this small data we have identified the zero easily and applied the formula but in big data it herculean task. So we need to apply AVERAGEIF function to take the average of numbers other than zero.
For this case again we need to make use of wild card characters. When the less than and greater than symbols are combined it tells the excel not to count that, so criteria will be “<>0”.
Like this, we can make use of AVERAGEIF function in excel to calculate the average based on some supplied criteria’s.
Things to Remember
- All the empty cells are ignored by the formula for [Average Range]
- We will get #DIV/0! Error if wrong criteria supplied.
- Logical values TRUE & FALSE are treated as 1 and 0.
- We can make use of wildcard characters.
This has been a guide to AVERAGEIF in Excel. Here we discuss how to calculate the average with the given criteria using an AVERAGEIF function along with practical examples and downloadable excel sheet. You can learn more about excel functions from the following articles –