Close the skill gap with the Financial Modeling & Valuation Analyst (FMVA)® Certification >> Enroll today and save!

AVERAGEIF Function

Calculates the average of all numbers in a range of cells based on a given criterion

What is the AVERAGEIF Function?

The AVERAGEIF Function is a Statistical function, which calculates the average a given range of cells given by a specific criterion. Basically, AVERAGEIF calculates central tendency, which is the location of the center of a group of numbers in a statistical distribution. The function was introduced in Excel 2007.

Formula

=AVERAGEIF(range, criteria, [average_range])

 

The AVERAGEIF function uses the following arguments:

  1. Range (required argument) – It is one or more than one cells that we want to average. The argument includes numbers or names, arrays, or references that contain numbers.
  2. Criteria (required argument) – Criteria determines the cell that will be averaged. Criteria can be in the form of an expression, number, cell reference, or text that defines which cells are averaged. For example, 12, “<12”, “Baby” or C2.
  3. Average_range (optional argument) – It is the actual set of cells that we wish to average. If a user omits it, the function will use the range given.

 

How to use the AVERAGEIF Function in Excel?

It is a built-in function that can be used as a worksheet function in Excel. To understand the uses of the AVERAGEIF function, let’s consider a few examples:

 

Step by Step Example of AVERAGEIF

Generally, AVERAGEIF is commonly used in finding the average of cells that are an exact match of a given criterion. From the table below, we wish to find out the sales of chocolate pastries. We are given the following data:

 

AVERAGEIF Function

 

The formula used to find the average is below:

 

AVERAGEIF Function - Example 1

 

We get the result below:

 

AVERAGEIF Function - Example 1a

 

If we wish to avoid typing the condition in the formula, we can type in a separate cell and provide a reference to the cell in the formula, as shown below:

 

AVERAGEIF Function - Example 1b

 

Example – How to find the average, with wildcard characters!

Let’s now see how we can use wildcards in AVERAGEIF function. Using the data above, but now I wish to find out the average sales of all types of pastries sold.

As in our search, the keyword is to be preceded by some other character, so we will add an asterisk in front of the word. Similarly, we can use it as the end of the word if the keyword will be followed by some other character.

Now, the formula we will use would be =AVERAGEIF(B5:B18, “*Pastries”, C5:C18)

 

AVERAGEIF Function - Example 2

 

 

We will get the result below:

 

AVERAGEIF Function - Example 2a

 

Suppose we wish to find out the average of all items except pastries, we will use the following formula =AVERAGEIF(A12:A15, “<>*(Pastries)”, B12:B15).

 

AVERAGEIF Function - Example 2b

 

Example – How to find an average, with empty or not empty criteria

Sometimes while doing data analysis in Excel, we may need to find an average of numbers that corresponds to either empty or non-empty cells.

So, in order to include blank cells in the formula, we will enter “=” that is the equal to sign. The cell will not contain anything that is not a formula or zero-length string.

 

AVERAGEIF Function - Example 3

 

For example, we are given the total cost of preparation of 3 items. Using =AVERAGEIF(B5:B7, “=”, C5:C7) formula, Excel will calculate an average of cell B5:B7 only if a cell in Column A in the same row is empty, as shown below:

 

AVERAGEIF Function - Example 3a

 

Suppose we wish to average values that correspond to blank cells and includes empty strings that are returned by any other function. In such scenario, we will use “” in the given criteria.

For example: =AVERAGEIF(B5:B7, “”, C5:C7)

 

AVERAGEIF Function - Example 3b

 

Now, suppose we wish to find the average of only those values that correspond to empty cells. In this scenario, we will use “<>” in the criteria.

So now using the same data, the formula to use is below:

 

AVERAGEIF Function - Example 3c

 

The result will be the average of Pastries & Dark Chocolates, that is 150.

 

AVERAGEIF Function - Example 3d

 

Things to remember about the AVERAGEIF Function 

  1. #DIV0! error – Occurs when:
    1. No cells in the range meet the criteria
    2. Range is a blank or text value
  2. When the given criteria is empty, AVERAGEIF treats it as 0 value.
  3. The use of wildcard characters such as the asterisk (*), the tilde(~) in the function as criteria, greatly enhances the search criteria. Example 2 shows a great way on how to use the wildcard characters .
  4. The function will ignore cells that contain TRUE or FALSE.

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!