Mastering Excel AVERAGEIF Function: A Comprehensive Guide to Conditional Averaging

Examples of the Excel AVERAGEIF Function

Here are few examples demonstrating the usage of the AVERAGEIF function in Excel:

Example 1: Basic Usage

Suppose you have a range of sales quantities in column B, and you want to find the average for those items whose sales are above or equal to 90. In cell D2, use the following formula:

In the above example, in the second argument ‘criteria’, we used the greater than or equal to (“>=90”) operator. Consequently, the average of the cells that are greater than 90 or equal to 90 from the range will be the output.

Note: Here, we omitted the third argument [average_range], so the calculation uses the cells in the provided range.

Example 2: Averaging a Specific Range

Now, suppose we have fruit items in column A and corresponding sales quantity in column B, and we want to find the average quantity for the item ‘Banana’. Use the formula:

Example 3: Using Criteria from a Cell

We will consider the above dataset in this example. We will explore a scenario where you don’t have to type the criteria every time; instead, you can use a cell reference.

In the example above, we didn’t do anything different. But instead of typing in the criteria each time, we used a cell reference (E1).

This is helpful because you don’t have to keep changing the formula. Just update the content in the referenced cell, and the formula will automatically adjust.

Example 4: Find the Average Using Wildcard Characters

Imagine you have a sales dataset for various fruits. Now, you want to find the average sales quantity not only for ‘Apple’ but for any item that includes the term ‘Apple’ in its name. Let’s explore how you can achieve this using the AVERAGEIF function with wildcard characters.

The formula =AVERAGEIF(A2:A11, “Apple”, B2:B11) calculates the average sales quantity for items containing the word “Apple” in the given data, using the wildcard (*) to match variations like “Apple” and “Custard Apple”.

Example 5: Find the Average Excluding Zero

Imagine you have a list of sales quantities for various products. Some entries contain zeros, representing instances where no sales occurred.

Now, you want to find the average sales quantity, excluding any instances where sales were zero. Let’s explore how you can achieve this using the AVERAGEIF function in Excel.

The formula =AVERAGEIF(B2:B11, “<>0”) calculates the average of numbers in the range B2:B11, excluding cells with the value 0.

Extra Notes

● If you don’t have valid numbers in what you are calculating, you will get an error: #DIV/0!

● If the provided criteria is empty, AVERAGEIF interprets it as a value of 0.

● Incorporating wildcard characters like the asterisk (*) or tilde (~) in the criteria significantly improves the search capabilities of the function. Example 4 demonstrates an effective use of wildcard characters.

● TRUE and FALSE values are disregarded when calculating an average.

● Empty cells are excluded from the average calculation.

● Excel AVERAGEIF function accommodates wildcards but operates in a case-insensitive manner.