An average is the most used mathematical function in our daily life.
It can be during the score calculation or financial maths, we end up using the average most of the times.
Excel AVERAGEIFS function can come handy during the calculations where you need to apply more than one filtration before finding the average.
Let’s see how to use it in detail…
1. What is the AVERAGEIFS Function?
2. What is the Syntax?
3. How to use it with an example?
1. What is the AVERAGEIFS Function?
An AVERAGEIFS function can be used to find the average of values supplied as arguments to it based on multiple criteria.
2. What is the Syntax?
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
- average_range = (Required) The actual range to average.
- criteria_range1 = (Required) The range on which criteria1 has to be applied.
- criteria1 = (Required) The criteria based on which the average has to happen.
- [criteria_range2]= (Optional) The range on which criteria2 has to be applied.
- [criteria2] = (Optional) (Required) The criteria based on which the average has to happen.
Note :
- Cells in the range that contain TRUE or FALSE are ignored.
- Any empty cells supplied as [average_range] will be ignored.
- If the value supplied to the range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
- If a cell containing empty value is supplied as criteria, AVERAGEIF treats it as a 0 value
- If no criteria are matched, then the result will be #DIV0! error.
- Wildcard characters such as question mark (?) and asterisk (*)also can be used in criteria.
- Each additional range must have the same number of rows and columns as the avg_range.
3. How to use it with an example?
Assuming we have the data of a few students along with their place, Joining date and scores.
Let’s find the average of their scores.
Average of numbers based on multiple criteria
**Explained**
=AVERAGEIFS(D3:D7,A3:A7,”>01/02/2019″,C3:C7,”India”)
- D3:D7 = range of cells containing numbers for which the average has to be calculated.
- A3:A7 = range of cells containing data on which the criteria1 has to be applied.
- “>01/02/2019″Â =Condition based on which the average has to happen.
- C3:C7 = range of cells containing data on which the criteria2 has to be applied.
- “India” = Condition based on which the average has to happen.
Hope it has helped you…
