Quick Links
Excel’s AVERAGEIF and AVERAGEIF functions let you find the average of a set of data. However, where they differ from the more straightforward AVERAGE function is in their ability to include or discount certain values within the calculation.
How to Use AVERAGEIF in Excel
Excel’s AVERAGEIF function allows you to calculate the mean of a dataset that has been filtered based on a single condition you set.
where
AVERAGEIF in Action
Let’s jump straight in and see how the AVERAGEIF function works in a real-world example.
Let’s suppose you have this Excel table containing 12 people’s names, ages, and salaries, and you’ve been asked to calculate the average salaries of people aged over 40.
In this case, thinking back to the syntax above, column B contains the values you want to test (argumentx), more than 40 is the criterion (argumenty) for the values in column B, and column C contains the values you want to average (argumentz).
and press Enter.
Let’s take a moment to break this formula down.
The first thing you’ll notice is the use ofstructured referencesfor argumentsxandz. In other words, rather than using direct cell references (such as B2:B13 for argumentxand C2:C13 for argumentz), the formula references the column headers. This is because the data is contained within aformatted Excel table, and the program defaults to using the table headers in formulas. This means that if you add extra rows to the bottom of your data, the formula will automatically include those new values.
Second, argumentyis contained within double quotations. Any time you use logical operators in Excel formulas—like equal to, greater than, or less than—they must be placed within double quotations.
Things to Note When Using AVERAGEIF
Before you go ahead and use AVERAGEIF in your own spreadsheet, there are some important points you should be aware of.
First, argumenty(the test) is very flexible. While the example above uses “>40” (a logical operator) to test the range in argumentx, there are various other types of criteria you could use instead:
=AVERAGEIF(Table1[Age],44,Table1[Salary])
The average salary of individuals aged 44
=AVERAGEIF(Table1[Person],“Jenny”,Table1[Salary])
The average salary of people whose name is Jenny
=AVERAGEIF(Table1[Person],“Jo*",Table1[Salary])
The average salary of anyone whose name starts with Jo-
Cell reference
=AVERAGEIF(Table1[Age],B15,Table1[Salary])
The average salary of anyone matching the age criterion in cell B15
Combinations of the above
=AVERAGEIF(Table1[Person],“Ja*",Table1[Salary])
The average salary of anyone whose name doesn’t start with Ja-
Second, the AVERAGEIF function doesn’t consider empty cells. For example, if someone’s salary value was blank, it would be ignored in the average calculation. However, if someone’s salary was $0, thiswouldbe included in the average calculation.
Finally, if none of the specified cells meets the test, Excel returnsthe #DIV/0! errorto tell you that it can’t calculate the average.
How to Use AVERAGEIFS in Excel
Where AVERAGEIF tests one condition before calculating the average of all values that meet the test, AVERAGEIFS allows you to narrow your results even further by using several criteria.
The AVERAGEIFS Syntax
It’s important to note that the order and number of arguments in the AVERAGEIFS function differs significantly from the AVERAGEIF function:
In other words, the syntax above represents the AVERAGEIFS function being used to create two tests (yandz), though you’re able to include up to 127 tests overall.
AVERAGEIFS in Action
If the AVERAGEIFS syntax confuses you, things will become much clearer when you see how the function works in an example.
This Excel table contains people’s names, ages, genders, and salaries, and your aim is to work out the average salary of all males over the age of 35.
Since there are two criteria (age and gender), you need to use the AVERAGEIFS function:
Things to Note When Using AVERAGEIFS
Here are some things to remember when using the AVERAGEIFS function:
AVERAGEIF and AVERAGEIFS aren’t the onlyfunctions in Excel that calculate the average of a dataset. For example, the AVERAGE function produces a straightforward arithmetic mean of a set of data, and the AVERAGEA function returns the mean of a set of numbers, text, and logical arguments.
