Today we are sharing the **erageifs function and its 5 common uses. Let's take a look at the details
a) Role. is a statistical function that calculates the average (arithmetic mean) of all cells that meet multiple conditions.
b) Grammar. **erageifs(**erage_range, criteria_range1, criteria1, [criteria_range2, criteria2],
Translated into Chinese is: **erageifs (Mean Region, Condition Region 1, Condition 1, [Condition Region 2, Condition 2],
Parameter: **erage range required. One or more cells to be averaged, which contain numbers or names, arrays, or references that contain numbers.
Criteria range1, criteria range2, etc. are required, and subsequent criteria ranges are optional. Regions from 1 to 127 in which the associated criteria are calculated.
Criteria1, criteria2, etc. are required, and subsequent criteria are optional. Conditions from 1 to 127 in the form of numbers, expressions, cell references, or text that define the cells for which the average will be calculated. For example, a condition can be represented as 2024"2024"、"<2024"、"Wu Song"or b4.
c) Description. If the Erage Range is a null value, a text value, or text that cannot be converted to a numeric value, the Erageifs returns an error value of div0! 。
If there are no cells that meet all the criteria, **erageifs returns an error value of div 0! 。
If a cell in the condition area is empty, Erageifs treats it as a value of 0.
Cells with true in the range are evaluated to 1;Cells with a range containing false are evaluated as 0 (zero).
Each cell in the **erage range is averaged only if it satisfies all the corresponding conditions specified for it.
*Each Criteria Range in Erageifs must be the same size and shape as the Sum Range.
You can use a wildcard in a condition, which is a question mark (?and an asterisk (*The question mark matches any single character; The asterisk matches any string of characters. If you want to find the actual question mark or asterisk, type a tilde (before the character.)
1.Calculate the instance that meets the average of the two criteria.
Example: Calculate the average grade of a female student in Class 2.
Formula: =**erageifs(d2:d10,a2:a10,f3,c2:c10,g3).
Description: It can be understood as follows: first screen out the students in the second class, then screen out the girls, and then add up the results of the screening and then divide by the number of girls screened out.
2.The region where the mean is calculated and the conditional region are instances of the same region.
Example: Find the average score of a student with a score greater than 70.
Formula: =**erageifs(d14:d22,d14:d22,">"&f15)
Note: In this example, the conditional region is the region where the average value is calculated.
3.An example of the same column multi-condition calculation average.
Example: Find the average score of a student with a language score between 70 and 80 in mathematics.
Formula: =**erageifs(d26:d34,c26:c34,">"&f27,c26:c34,"
Description: Two conditional regions are in the same region.
4.Two instances of the average of the two conditions for the date.
Example: Find the average sales volume for the period 2023 12 4-2023 12 8.
Formula: =**erageifs(c38:c46,d38:d46,">="&f39,d38:d46,"<="&g39)
5.Wildcard ?or *.
Example: Find the average grade of a student with the surname Kong.
Formula: =**erageifs(d50:d58,b50:b58,"Hole*")
Note: The question mark matches any single character. The asterisk matches any string of characters. If you want to find the actual question mark or asterisk, type a tilde (before the character.)
The above is an example of the **erageifs function and its usage, have you mastered it? If you have seen it a thousand times, it is better to pass it with your hands once; What you learn is what you earn, so let's try it out
Send a private message to "Exercise" to get the exercise file of this article.
Check it outBest practices for excel chartsColumn Learning**Data Visualization Practical Dry Goods Article:
List of high-quality authors