How to use averageifs in excel

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.

averageifs - How to use averageifs in excel

1.1 Calculate the average of the employee’s salary greater than 180,000,000 and less than 214,000,000.

Syntax: =AVERAGEIFS(E2:E9,E2:E9,”>180000000″,E2:E9,”<214000000″)

Result:

averageifs excel - How to use averageifs in excel

1.2 Calculate the average of bounty money of male employees with bounty level greater than 500000

Syntax: =AVERAGEIFS(E2:E9,E2:E9,”>180000000″,E2:E9,”<500000″)

Result:

excel averageifs - How to use averageifs in excel

1.3 On average, the male employee’s Bounty is greater than 260,000 and the salary is greater than 150,000,000

Syntax: =AVERAGEIFS(F2:F9,F2:F9,”>260000″,D2:D9,”Male”,E2:E9,”>150000000″)

Result:

averageifs function - How to use averageifs in excel

1.4 On average, the female employee’s salary is greater than 187,000,000.

READ:  How to use the Excel MID function

Syntax: =AVERAGEIFS(E2:E9,E2:E9,”>187,000,000″,D2:D9,”Female”)

Result:

averageifs function in excel - How to use averageifs in excel

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.

Add a Comment

Your email address will not be published. Required fields are marked *