What is the AVERAGEIFS function in Excel?
→ The AVERAGEIFS function returns the average value (arithmetic mean) of all numbers in a range of cells, that meet one or more criteria.
→ The criteria used for AVERAGEIFS can include logical operators like greater than (>), less than (<), not equal to (<>), and equal to (=), and wildcards (*,?) for partial matching.
→ We can use the AVERAGEIFS function when we want to make an average of selected numbers after comparing a single condition or more than one condition at the same time.
→ We can put the AVERAGEIFS function in the Statistical Function Category
How to use the AVERAGEIFS function in Excel?
→ We can use the AVERAGEIFS function in MS Excel just by entering the formula in a spreadsheet.
What is the return value of the AVERAGEIFS function?
→ The AVERAGEIFS function will return the numeric value.
What is the syntax of the AVERAGEIFS function?
→ The syntax for the AVERAGEIFS function in Microsoft Excel is:
=AVERAGEIFS(average_range,criteria_range1,criteria1, [criteria_range2],[criteria2], ...)
The Syntax Parameter or Syntax Arguments are:
→ Average_range - This is the cell range from which we want to make the average based on defined criteria.
→ criteria_range1 - The range of cells that we want to apply criteria1 against.
→ criteria1 - It is used to determine which cells to be taken for average. criteria1 is applied against criteria_range1.
→ criteria_range2 ... criteria_range_n – This is an optional command if we want to add more than one criterion then it will be used. It is the range of cells that you want to apply criteria2 ... criteria_n against the criteria_range. We can add up to 127 ranges.
→ criteria2, ... criteria_n – This is an optional command. It is used to determine which cells need to make an average. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. We can add to 127 criteria.
Examples of AVERAGEIFS Function:
→ For a better understanding of the AVERAGEIFS function, we can take one example.
→ In the below picture, we can see the different cars sold in different years.
→ So we want how many average Toyota cars were sold per year during 2016, 2017, and 2018.
→ In this example, we have multiple criteria for comparison so we will use the AVERAGEIFS function.
→ Example: We need to find the average qty of Toyota Car sold per year during 2016, 2017, and 2018.
→ So, from the syntax, we will use the below formula for the find the average qty of Toyota Car sold per year during 2016, 2017, and 2018.
=AVERAGEIFS(C2:C10,B2:B10,"Toyota",A2:A10,"2017")
➨ From the above formula,
- average_range = C2:C10 (find the average qty of Toyota Car sold per year during 2016, 2017, and 2018.)
- criteria_range1 = B2:B10 (Check criteria1 “Toyota” from this range.)
- criteria1 = “Toyota”
- criteria_range2 = A2:A10 – (Check criteria2 “2017” from this range.)
- criteria2 = “2017”
→ So with the help of the AVERAGEIF function, we have found that the average car qty sold per year is 333. (If you are getting the answer 333.33 so convert it to round up or down value because 0.33 qty is not a possible value for car qty)
→ Also, we can use cell reference instead of the criteria’s absolute value in the above formula as mentioned in the below formula.
=AVERAGEIFS(C2:C10,B2:B10,D3,A2:A10,D4)
→ So we have used cell reference (D3) instead of absolute value (Toyota) and used to mention (D4) instead of "2017" in the above example.
→ We are getting the same answer for the average car qty sold per year is 333 if we are using a cell reference method instead of an absolute reference method.
👉 For a regular update:
➨ Join us (Telegram Channel)
➨ Join us (LinkedIn Page)
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment