AVERAGEIF
The AVERAGEIF function returns the average (arithmetic mean) of the cells in a set that meet a given condition.
AVERAGEIF(test-values, condition, avg-values)
test-values: A collection containing the values to be tested. test-values can contain any value.
condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. condition can include comparison operators, constants, the ampersand concatenation operator, references, and wildcards. You can use wildcards to match any single character or multiple characters in the expression. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard. condition can also contain a REGEX function instead of wildcards.
avg-values: An optional collection containing the values to be averaged. avg-values is a reference to a single collection of cells, which may contain only number values or boolean values.
Notes
Each value is compared to condition. If the value meets the conditional test, the corresponding value in avg-values is included in the average.
avg-values and test-values (if specified) must be the same size.
If avg-values is omitted, test-values is used for avg-values.
If avg-values is omitted or is the same as test-values, test-values can contain only number values or boolean values.
If more than one condition or set of test-values is required, consider using the AVERAGEIFS function.
Examples |
|---|
Given the following table: |
A | B | C | D | |
|---|---|---|---|---|
1 | Age | Sex | Status | Salary |
2 | 35 | M | M | 71,000 |
3 | 27 | F | M | 81,000 |
4 | 42 | M | M | 86,000 |
5 | 51 | M | S | 66,000 |
6 | 28 | M | S | 52,000 |
7 | 49 | F | S | 62,000 |
8 | 63 | F | M | 89,000 |
9 | 22 | M | M | 34,000 |
10 | 29 | F | S | 42,000 |
11 | 35 | F | M | 56,000 |
12 | 33 | M | S | 62,000 |
13 | 61 | M | M | 91,000 |
=AVERAGEIF(A2:A13, "<40", D2:D13) returns approximately 56,857, the average salary of people under the age of 40. =AVERAGEIF(B2:B13, "=F", D2:D13) returns 66,000, the average salary of females (indicated by an "F" in column B). =AVERAGEIF(C2:C13, "S", D2:D13) returns 56,800, the average salary of people who are single (indicated by an "S" in column C). =AVERAGEIF(A2:A13, ">=40", D2:D13) returns 78,800, the average salary of people who are 40 or older. You can also use the ≥ operator. =AVERAGEIF(C2:C13, "<>M", D2:D13) returns 56,800, the average salary of people who are not married. You can also use the ≠ operator |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | |
2 | 41 | Aaron |
3 | 29 | |
4 | 64 | |
5 | 12 | Sarah |
=AVERAGEIF(B1:B5, REGEX("([A-Z0-9a-z._% -] )@([A-Za-z0-9.-] \.[A-Za-z]{2,4})"), A1:A5) returns 46, the average of all the cells in A1:A5 where the corresponding cell in B1:B5 contains an email address. |