How to use AVERAGEIFS function on Excel
AVERAGE is the basic Excel function, used to calculate the average of the values in the data table. And when using the advanced AVERAGE function, users can combine more to calculate the various contents such as, AVERAGEIF function to average with 1 condition, or DAVERAGE function. In the following article, we will guide you how to use the AVERAGEIFS function, calculating the average value combining various conditions in the data table.
- How to combine Sumif and Vlookup functions in Excel
- How to use Lookup function in Excel
- How to use Vlookup function in Excel
Instructions for using the AVERAGEIFS Excel function
AVERAGEIFS function on Excel has a function formula is AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], .) .
Inside:
- average_range: The data area that needs to calculate the average value includes the reference number or name or array containing the number, which is the required parameter.
- criteria_range1, criteria_range2: The condition containing the total condition, criteria_range1 is the required parameter, the remaining criteria_range options up to 127 arguments, are required parameters.
- criteria1, criteria2, .: As a condition to average plus, riteria1 is a required parameter, other criteria optionally contain a maximum of 127 conditions.
Note when using functions:
- If the value of the argument contains a logical value or a blank cell, that value will be ignored.
- If the range is empty or textual, the function returns the # DIV / 0 error value.
- If the cells in Criteria_range are empty, the default function is 0.
- If the value in the data cell is logical value = True, it is considered as 1, if False value is considered 0.
- If the values in average_range cannot be converted to numbers then the error # DIV / 0 is reported.
- If no value meets all conditions, the error function # DIV / 0!
- Can use alternate characters like *.?. in terms of average calculation.
We will take the example with the data table as shown below.
Example 1 : Calculate the average of employees' wages greater than 3,000,000 and less than 7,000,000.
Step 1:
You enter the formula = AVERAGEIFS (D2: D6, D2: D6, "> 3000000", D2: D6, "<7000000") .
Where D2: D6 is the data column to be calculated and D2: D6, "> 3000000", D2: D6, "<7000000" are conditions in that data area.
Step 2:
It will then display the average result plus the data based on the established condition. The calculated amount is 5,433,333.
Example 2 : Calculating the average of bonuses of male employees with a salary of 250,000.
Step 1:
Also in the input box, users enter the formula to calculate the value = AVERAGEIFS (E2: E6, E2: E6, "250000", C2: C6, "Nam") .
Where E2: E6 is the area to calculate the value, E2: E6, "250000" is the first condition for the value in that region. C2: C6, "Male" is the second condition with another data area.
Step 2:
When pressing Enter, the result is 250,000 as shown below.
Example 3 : Calculate the average of the salaries of male employees larger than 200,000 and wages greater than 4,500,000.
Step 1:
In the input box we enter the formula = = AVERAGEIFS (E2: E6, E2: E6, "> 200000", C2: C6, "Male", D2: D6, "> 4500000") .
Where E2: E6 is the data area to calculate the average value, E2: E6, "> 200000" is the first condition in the data area E2: E6, C2: C6, "Male" is the second condition at Data area C2: C6, D2: D6, "> 4500000") is the 3rd condition in the data area D2: D6.
Step 2:
Press Enter and the result will be 375,000 as shown below. Because only 2 male employees meet all 3 conditions in the request.
Example 4 : Calculate the average salary of female employees with wages greater than 7,500,000.
Step 1:
Also in the box that displays the results, the user enters the formula for calculating AVERAGEIFS = = AVERAGEIFS (D2: D6, D2: D6, "> 7500000", C2: C6, "Female") and then press Enter.
Step 2:
The result will indicate error # DIV0! Since only one condition is satisfied in the formula, no cell satisfies all of the two conditions in the formula.
Above is how to apply the AVERAGEIFS function in Excel to calculate the average value with many different conditions. In general, the usage is very simple, you need to enter the area to calculate the value first, then the conditions on each different data area.
See more:
- Summary of trigonometric functions in Excel
- How to automatically display names when entering code in Excel
- Calculation of age in Excel
I wish you all success!
You should read it
- How to use the DAVERAGE function in Excel
- Basic Excel functions that anyone must know
- AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel
- Average function (calculate the average) in Excel
- How to use the AVERAGE function in Excel
- How to use AVERAGEIF function in Excel
- How to calculate the average in Excel
- How to use the SUBTOTAL function in Excel
- GEOMEAN function - The function returns the average of a positive array or range of data in Excel
- AVERAGE function - The function returns the average of the arguments in Excel
- TRIMMEAN function - The function returns the average of the inner part of a dataset in Excel
- How to use the SUM function to calculate totals in Excel
Maybe you are interested
How to turn a photo into a painting using the Generative Fill function in Photoshop
How to use the TREND function in Excel
Google Sheets Functions to Simplify Your Budget Spreadsheets
Instructions for using the TRIMRANGE function to clean up Excel tables
How to master numerical data in Google Sheets with the AVERAGE function
Don't buy headphones if they lack this important function!