The AVERAGE function is a basic Excel function, used to calculate the average of values in a data table. And when using advanced AVERAGE function, users can combine to calculate a variety of contents such as, AVERAGEIF function to calculate the average plus 1 condition, or the DAVERAGE function. Here’s how to use the AVERAGEIFS function to calculate the average value combining many different conditions in a data table. This article will give you the formula of AVERAGEIFS and specific examples for you to grasp.
Syntax: = AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The AVERAGEIFS function syntax has the following arguments:
- Average_range (Required) One or more cells to average, including numbers or names, arrays, or references that contain numbers.
- Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria.
- Criteria1, criteria2, … Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
Example: We will take an example with the data table as below.
1.1 Calculate the average of the employee’s salary greater than 180,000,000 and less than 214,000,000.
1.2 Calculate the average of bounty money of male employees with bounty level greater than 500000
1.3 On average, the male employee’s Bounty is greater than 260,000 and the salary is greater than 150,000,000
1.4 On average, the female employee’s salary is greater than 187,000,000.
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 calculated area first, then the condition on each different data area.