AVERAGEIF Function in Excel
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.
We have numerous excel functions to work in different situations. The average function is used to calculate the average of the range of 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 the AVERAGEIF function. In this article, we will take you through the function in excel, i.e., AVERAGEIF.
Let me explain to you the syntax of the AVERAGEIF function. It has 3 arguments to deal with; below are the criteria.
- 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 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.
What Does AVERAGEIF Function Do in Excel?
AVERAGEIF function calculates the average of specified numbers but also based on supplied criteria.
For example, look at the below data sample.
From this data table, we can easily tell what the average sales are by applying the AVERAGE functionApplying The AVERAGE FunctionThe AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function. The average formula is =AVERAGE(.
So, the average sale is 180 from the above data table. This is easy, but how do you tell what the average of the product “A” only is??
This is where the AVERAGEIF function will help us. Here criteria to calculate the average is “Product A.”
How to use AverageIF Function in Excel? (with Examples)
Let’s take the same data as an example.
From this sales table, we need to find the average sales of the product “A.” Follow the below steps to calculate the average.
- Open the AVERAGEIF function in one of the cells.
- Select the range as product list, i.e., from A2 to A10.
- 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.”
- Next up for which numbers we need to find the average. We need to find the average for the sales column.
- Close the bracket and hit enter; we will get the average sale for the product “A.”
So, the AVERAGEIF 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 the 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 the average.
- Step 1: Open AVERAGEIF function in one of the cells.
- Step 2: Select RANGE as the 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 the enter key to get the average units sold number when the unit price is >=25.
So, when the unit priceUnit PriceUnit Price is a measurement used for indicating the price of particular goods or services to be exchanged with customers or consumers for money. It includes fixed costs, variable costs, overheads, direct labour, and a profit margin for the organization. is >=25, average sales are 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 useful in excel functionsWildcard Characters Are Useful In Excel FunctionsIn Excel, wildcards are the three special characters asterisk, question mark, and tilde. Asterisk denotes multiple characters, a question mark denotes a single character, and a tilde denotes the identification of a wild card character.. 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 an average of 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 the 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 the AVERAGEIF function to calculate the average based on some supplied criteria.
Things to Remember
- All the empty cells are ignored by the formula for [Average Range]
- We will get #DIV/0! Error if the wrong criteria are 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 a downloadable excel sheet. You can learn more about excel functions from the following articles –