AVERAGEIFS Function: Excel is a spreadsheet program that is developed for Windows, Mac, Android, and iOS. It helps to perform calculations on your PC. The purpose of this software is to do complicated calculations which are difficult to do manually. If you want to calculate the average value of the set of numbers based on one or two criteria, you can use the AVEARAGEIFS function.
In this tutorial, we will guide you to learn the usage of Excel AVERAGEIFS Function with its syntax, explanation, and examples. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Explanation of AVERAGEIFS Function
- It is one of the built-in functions in Microsoft Excel.
- This function returns the average of a set of input values that meet the multiple criteria.
- The AVERAGEIFS function is similar to the AVERAGEIF function.
- The criteria can be defined using logical operators such as (<, >, =, <>, *, ?).
Syntax
- Here, you will see the syntax of the AVERAGEIFS function.
- To apply this function on your spreadsheet, you have to select a cell and enter the formula in the following format.
- Once you enter the formula, just click on the Enter button to get the result.
=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], …)
Arguments Explanation:
- Average_range – It is the range to find the average.
- Range 1 – It is the range of input values that contain numbers.
- Criteria 1 – It is the criteria to use in range 1.
- Range 2 – It is an optional one. It is the second range to find the average.
- Criteria 2 – It is an optional one. The second criteria to use in range 2.
Note:
1) This function ignores the text, Empty cells, and special characters.
2) The arguments can be Numbers, Array, or References that contain numbers.
3) This function evaluates logical values TRUE & FALSE as 1 and 0.
4) If the range is blank or text value and then this function returns #DIV/0! Error.
5) If any cell in the range doesn’t meet the given condition or criteria, then this function returns #DIV/0! Error.
Examples
Let’s see some examples to understand this function easily.
- In the below screenshot, you can be able to see the input range 1 in Column C, and range 2 in Column D, and their criteria are “>80” and “B” which means the student’s score should be greater than 80 and the rank should be B.
- The output average value will display in the Cell F3. Apply the above-mentioned formula.
- Finally, we need to click on the Enter button to get the result.
- This function ignores the text, Empty cells, and special characters.
- If the range is blank or text value and then this function returns #DIV/0! Error.
- If any cell in the range doesn’t meet the given condition or criteria, then this function returns #DIV/0! Error.
From the above examples, you can learn how to use the AVERAGEIFS function.
Closure
In this tutorial, we guided you to learn the usage of the Excel AVERAGEIFS Function with its syntax, explanation, and examples. Leave your feedback in the comment section. To learn more about Excel functions, then visit our webpage Aawexcel.com.
Video Tutorial
The following video will show you how to use the Excel AVERAGEIFS function in the spreadsheet.
Keep Reading:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Deepak is an Excel enthusiast and data analyst with over 5 years of experience in the field. I’m a renowned author for AAW Excel who connects with a committed group of Excel users by imparting his professional knowledge and helpful advice. No matter your level of Excel proficiency, my articles and tutorials will teach you something new and beneficial. Connect me via my social links!… Mastodon & More!
