AVERAGEIF Function
Calculates the average of all numbers in a range of cells based on a given criterion
Calculates the average of all numbers in a range of cells based on a given criterion
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.
=AVERAGEIF(range, criteria, [average_range])
The AVERAGEIF function uses the following arguments:
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:
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:
The formula used to find the average is below:
We get the result below:
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:
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)
We will get the result below:
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).
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.
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:
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)
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:
The result will be the average of Pastries & Dark Chocolates, that is 150.
Click here to download the sample Excel file
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:
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!