How to Use the AVERAGEIF and AVERAGEIFS Functions in Excel

Excel's AVERAGEIF and AVERAGEIFS functions quick review

Excel's AVERAGEIF and AVERAGEIFS functions can be used to calculate the average value of a dataset. However, unlike simpler AVERAGE functions, they are able to include or exclude specific values ​​in the calculation.

How to use the AVERAGEIF function in Excel

Excel's AVERAGEIF function allows you to calculate the average value of a filtered dataset based on a single condition set.

AVERAGEIF function syntax

The AVERAGEIF function contains three parameters:

<code>=AVERAGEIF(x,y,z)</code>

Of:

• x (required) is the cell area to be tested according to the conditions;
• y (required) is a condition (testing parameter x);
• z (optional) is the cell area to be averaged if the test in parameter y meets the criteria. If you omit z, Excel averages the values ​​identified in parameter x.

AVERAGEIF Function Practical

Let's take a look at an example of the AVERAGEIF function in practical applications.

Suppose you have an Excel table with the names, ages and salaries of 12 people, you need to calculate the average salary of people over 40.

In this case, recall the syntax above, column B contains the value you want to test (parameter x), greater than 40 is the condition for the value of column B (parameter y), and column C contains the value you want to average (parameter z).

Therefore, you need to enter:

in a separate cell:

<code>=AVERAGEIF(Table1[Age],">40",Table1[Salary])</code>

and press Enter.

Let's take some time to break down this formula.

First, you will notice that structured references are used for parameters x and z. In other words, the formula refers to the column header, rather than using the cell reference directly (for example, use B2:B13 for parameter x and C2:C13 for parameter z). This is because the data is contained in a formatted Excel table, and the program uses the table title in the formula by default. This means that if you add extra rows to the bottom of your data, the formula will automatically include these new values.

Secondly, the parameter y is included in double quotes. When using logical operators in Excel formulas (for example, equal to, greater than, or less), they must be placed in double quotes.

Things to note when using AVERAGEIF function

Before you use AVERAGEIF in your spreadsheet, there are some important points to be paid attention to.

First of all, the parameter y (test) is very flexible. Although the above example uses ">40" (logical operator) to test the range in parameter x, you can use various other types of conditions:

条件类型	示例公式	计算结果
值	=AVERAGEIF(Table1[Age],44,Table1[Salary])	44 岁个人的平均工资
文本	=AVERAGEIF(Table1[Person],"Jenny",Table1[Salary])	姓名为 Jenny 的人的平均工资
通配符	=AVERAGEIF(Table1[Person],"Jo",Table1[Salary])	姓名以 Jo 开头的任何人的平均工资
单元格引用	=AVERAGEIF(Table1[Age],B15,Table1[Salary])	与 B15 单元格中的年龄条件匹配的任何人的平均工资
上述条件的组合	=AVERAGEIF(Table1[Person],"Ja",Table1[Salary])	姓名不以 Ja 开头的任何人的平均工资

Secondly, the AVERAGEIF function does not take into account empty cells. For example, if someone's salary value is blank, it is ignored in the average calculation. However, if someone’s salary is $0, will include in the average calculation.

Lastly, if no specified cell meets the test conditions, Excel returns a #DIV/0! error to tell you that it cannot calculate the average.

How to use the AVERAGEIFS function in Excel

AVERAGEIF Tests a condition before calculating the average value that satisfies all values ​​tested, while AVERAGEIFS allows you to further narrow the results by using multiple conditions.

AVERAGEIFS function syntax

It should be noted that the order and number of parameters in the AVERAGEIFS function are very different from those in the AVERAGEIF function:

<code>=AVERAGEIF(x,y,z)</code>

Of:

• x (required) is the area of ​​cells containing the values ​​to be averaged;
• y¹ (required) is the cell area containing the first value to be tested;
• y² (required) is a test of y¹;
• z¹ (optional) is the cell area containing the second value to be tested;
• z² (required if z¹ is included) is a test of z¹.

In other words, the above syntax means using the AVERAGEIFS function to create two tests (y and z), but you can include up to 127 tests in total.

AVERAGEIFS Function Practical

If the AVERAGEIFS syntax confuses you, things get clearer when you see how the function works in the example.

This Excel form contains the name, age, gender, and salary of the person, and your goal is to calculate the average salary for all men over 35 years of age.

Because there are two conditions (age and gender), you need to use the AVERAGEIFS function:

<code>=AVERAGEIF(Table1[Age],">40",Table1[Salary])</code>

Of:

• Table2[Salary] is the area of ​​cells containing the value to be averaged (parameter x);
• Table2[Age] is the cell area (parameter y¹);
• ">35" is a test of the first range (parameter y²);
• Table2[Gender] is the cell area (parameter z¹);
• "M" is a test for the second range (parameter z²).

Things to note when using AVERAGEIFS function

When using the AVERAGEIFS function, remember the following points:

• The test can be logical parameters, values, text, cell references, or a combination of these.
• Logical parameters and text must always be enclosed in double quotes.
• Only cells that meet the all conditions are met will be included in the average calculation.
• All areas specified in the AVERAGEIFS formula must be the same size.
• The AVERAGEIFS function ignores empty cells but contains cells with a value of zero.
• If no cell meets all conditions, a #DIV/0! error will be returned.

---

AVERAGEIF and AVERAGEIFS are not the only functions in Excel that calculate the average value of the dataset. For example, the AVERAGE function generates a direct arithmetic average of the dataset, while the AVERAGEA function returns the average of numbers, text, and logical parameters.

The above is the detailed content of How to Use the AVERAGEIF and AVERAGEIFS Functions in Excel. For more information, please follow other related articles on the PHP Chinese website!