How To Use AverageIf Function in Excel Easy Ways

In this article, we will describe the AVERAGEIF function in excel, how we can use it, and how examples help in understanding this topic.

Still, it is the continuation of average function, but before proceeding with the methods and examples, let’s defined first the excel averageIf function.

What is the AVERAGEIF Function?

The AVERAGEIF Function is a statistical function which calculates the average of the range of cells with a true or false condition. Moreover, it is a function that yields according to its specific or multiple criteria. Criteria of this function could be supplied as numbers, strings, or references.

Primarily, AVERAGEIF calculates central tendency, which is the location of the center of a group of numbers in a statistical distribution. This was introduced in Microsoft Excel 2007, hence this is not functional in older versions.

The condition is referred to as criteria, which can check things like:

• If a number is greater than another number >.
• If a number is smaller than another number <.
• If a number or text is equal to something =.

Example AVERAGEIF Function

In the example shown the formulas in B2:E10 are as follows:

=AVERAGEIF(F2:F10,"<79") // grade is failed
=AVERAGEIF(F2:F10,">79") // Passed
=AVERAGEIF(F2:F10,">=85",B2:E10) // Add five points 
=AVERAGEIF(B2:E:10,">=90",F2:F10) // Add 10 points

How to do an AVERAGEIF Function in Excel

How to use AVERAGEIF Function in Excel

Microsoft Excel has provided a built-in function that can be used in a spreadsheet. So here, we’ll now look at how to utilize the following examples and steps provided.

As AVERAGEIF discussed above, the main idea of it is to find the average of the cells in an exact match of criterion. For instance, here we wish to find the average of students’ grades.

Example 1:

We provide here the given sample data:

The formula used to find the average is below:

Wildcards

Now in creating criteria in AverageIf function Excel you can use the wildcards characters. Namely, characters question mark (?), asterisks(*), or tilde (~). Additionally, the question mark(?) matches one character and the asterisk matches zero or more characters of any kind.

For example, to average cells in a B1:B10 when cells in A1:A10 contain the text “Smith” anywhere, you can use a formula like this:

=AVERAGEIF(A1:A10,"*Smith*",B1:B10)

Things to remember about the AVERAGEIF Function 

1. The AVERAGEIF treats 0 as value if the given criteria is empty.
2. #DIV0! error – Occurs when:
   • No cells that meet the range of the criteria.
   • Range is empty or string value.
3. The function will ignore cells that contain TRUE or FALSE.
4. The use of wildcard characters such as the asterisk (*), the tilde(~) in the function as criteria, greatly enhances the search criteria.

Summary

In summary, we discussed and learn the AVERAGEIF Function with the following concept:

• Average_range does not have to be the same size as range. The top left cell in average_range is used as the starting point, and cells that correspond to cells in range are averaged.
• Cells in range that contain TRUE or FALSE are ignored.
• Empty cells are ignored in range and average_range when calculating averages.
• AVERAGEIF returns #DIV/0! if no cells in range meet criteria.
• AVERAGEIF allows the wildcard characters question mark (?) and asterisk (*), in criteria. The question mark ? matches any single character and the asterisk(*) matches any sequence of characters. To find a literal (?) or *, use a tilde (~) before the character, i.e. ~* and ~?.