Averageif In Excel How To Use Averageif Function In Excel

AVERAGEIF Function in Excel

For example, suppose we have a product dataset, and we need to know the product sales average. Using the Excel AVERAGEIF function, we can calculate the average of all the sales in a range of cells based on the specific criteria.

We have numerous Excel functions to work in different situations. For example, the AVERAGE function calculates the average of the range of cell values. Similarly, we can also calculate the average of specific values based on other columns. 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Excel AVERAGEIF Function (wallstreetmojo.com)

Syntax

Let me explain to you the syntax of the AVERAGEIF function. It has three 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, we need to take the average for which product out of all the products.[Average Range]: We need to select the number column we want to calculate the average.

What Does AVERAGEIF Function Do in Excel?

The AVERAGEIF function calculates the average of specified numbers but is also based on supplied criteria.

For example, look at the below data sample.

We can easily tell the average sales from this data table 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(read more.

So, the average sale is 180 from the above data table. It is easy, but how do you tell what the average of the product “A” is?

That is where the AVERAGEIF function will help us. Here the criteria to calculate the average is Product “A.”

How to use the AverageIF Function in Excel? (with Examples)

Example #1

Let us take the same data as an example.

So, the AVERAGEIF function calculates the average only for the Product “A” is 200.33, whereas the overall average is just 180.

We must first open the AVERAGEIF function in one of the cells. We must select the range as a “Product” list, from A2 to A10. So, we need to find the average for which product is out of a selected range of products. In this case, we need to find the average for Product A. Next up for which numbers do we need to find the average. We need to find the average for the sales column. After that, we must close the bracket and press the “Enter” key. As a result, we will get the average sale for Product “A.”

Example #2

Now, look at the below data. Again, we have a product name, price, and the number of units sold in this data.

So, from the above list, we need to calculate the average unit sold when the unit price is >=25. So, we need to use the operator key greater than (>) in the criteria argument. Then, follow the below steps to calculate the average.

Step 1: First, we must open the 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, we must enter the criteria as “>=25.”
Step 4: The average range is the “Unit Sold” column from C2 to C10.
Step 5: We are done with the formula. Close the bracket and press the “Enter” key to get the average unit 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.read more 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 average sales of price is only 1,917.

Example #3

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.read more. One of the problems with the 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. So, we have five numbers from B2 to B6, but zero. So, we get the increased average when we calculate the average excluding zero.

So, we got an average of 7 now. But in this small data, we have easily identified the zero and applied the formula, but it is an arduous task in big data. So, we need to use the AVERAGEIF function to take the average of numbers other than zero.

For this case again, we need to use wild card characters. When the less than and greater than symbols are combined, it tells Excel not to count that so that the criteria will be “<>0.”

Like this, we can use 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” and “FALSE” are treated as 1 and 0.We can use wildcard characters.

AVERAGEIF Function in Excel#

Syntax#

What Does AVERAGEIF Function Do in Excel?#

How to use the AverageIF Function in Excel? (with Examples)#

Example #1#

Example #2#

Example #3#

Things to Remember#

Recommended Articles#