AVERAGEIF function

Returns the average (arithmetic mean) of the array elements that satisfy a condition. AVERAGEIF({ 3, 4, 5 }, ">3")AVERAGEIF({ 3; 4; 5 }; ">3") returns 4.5 ((4 + 5) / 2(4 + 5) / 2) and not 4 ((3 + 4 + 5) / 3(3 + 4 + 5) / 3), as only 4 and 5 are greater than 3, as specified by the ">3"">3" parameter.

Use & to reference values that reside elsewhere. This formula only considers those array elements which are greater than the value of Field1:

AVERAGEIF can use separate arrays, of equal size, for determining which elements to consider and for determining which numbers to multiply together. This version is described further down. Use AVERAGEIFS to use multiple conditions (or use AVERAGEIF with a formula fragment and &&, described below).

Writing conditions

AVERAGEIF supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = and == (equality), <> and != (inequality).

If the second parameter is set to "", only blank values are considered. If it is set to "<>" or "!=", all non-blank values are considered.

If the operator is left out, "=" is assumed. As a result, these formulas are equivalent:

The language settings of the app are taken into account when parsing numbers that are part of the condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").

Specifying the condition using a formula fragment

The second parameter can also be a formula fragment. These formulas are equivalent, with the second one using a formula fragment:

Using a formula fragment gives you access to the full power of the formula language. Consider this formula:

The formula above returns 5, because only array elements are considered which are greater than 3 and are odd numbers, which disqualifies 3 and 4, leaving only 5.

&& means logical "and," || means logical "or" and ! means logical negation.

This formula returns 4 ((3 + 4 + 5) / 3(3 + 4 + 5) / 3), as 3, 4 and 5 are all considered:

3, 4 and 5 are all considered due to the || operator, which causes all array elements to be considered which are either greater than 3 or are odd.

This formula returns 4, as only 4 is both greater than 3 and is "not odd" (even):

! in !ISODD(Element)!ISODD(Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.

The formula fragment is run once for every array element to be tested, and is expected to return TRUE if the element should be considered, and FALSE otherwise. To do its work, it has access to the values Element (the array element currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source, the source array.

Using FILTER, AVERAGE and REDUCE instead of AVERAGEIF

The work AVERAGEIF performs can be divided into two parts: filtering out unwanted array elements and then returning the average of the array elements which made the cut. As such, these formulas are equivalent:

The version using FILTER provides more flexibility, as the final step (calculating the arithmetic mean through AVERAGE) can be replaced by another function (like SUM, which adds the remaining numbers together), independent of the filtering logic.

For even more flexibility, consider replacing SUM with REDUCE. Refer to the example below for more information.

Examples

Returns the average (arithmetic mean) of all array elements which satisfy a condition. AVERAGEIF({ 3, 4, 5 }, ">3", { 30, 40, 50 })AVERAGEIF({ 3; 4; 5 }; ">3"; { 30; 40; 50 }) returns 45 ((40 + 50) / 2(40 + 50) / 2) and not 40 ((30 + 40 + 50) / 3(30 + 40 + 50) / 3), as only 4 and 5 of the first array are greater than 3, as specified by the ">3"">3" parameter.

Use & to reference values that reside elsewhere. This formula only considers those elements of the second array which are greater than the value of Field1:

AVERAGEIF can use the same array for determining which elements to consider and for determining which numbers to multiply together. This version is described further up. Use AVERAGEIFS to use multiple conditions (or use AVERAGEIF with a formula fragment and &&, described below).

Beyond numbers

The first array does not need to be a number array. This formula returns 35 ((30 + 40) / 2(30 + 40) / 2), as only 30 and 40 correspond to "Eve" in the first array:

Writing conditions

AVERAGEIF supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text).

If the second parameter is set to "", only the blank values of the second array are considered. If it is set to "<>" or "!=", all non-blank values are considered.

If the operator is left out, "=" is assumed. As a result, these formulas are equivalent:

The language settings of the app are taken into account when parsing numbers that are part of the condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").

Specifying the condition using a formula fragment

The second parameter can also be a formula fragment. These formulas are equivalent, with the second one using a formula fragment:

Using a formula fragment gives you access to the full power of the formula language. Consider this formula:

The formula above returns 50, because only array elements are considered which are greater than 3 and are odd numbers, which disqualifies 3 and 4, leaving only 5 (corresponding to 50 in the second array).

&& means logical "and," || means logical "or" and ! means logical negation.

This formula returns 40 ((30 + 40 + 50) / 3(30 + 40 + 50) / 3), as 3, 4 and 5 are all considered:

3, 4 and 5 are all considered due to the || operator, which causes all array elements to be considered which are either greater than 3 or are odd (3, 4 and 5 correspond to 30, 40 and 50 in the second array).

This formula returns 40, as only 4 is both greater than 3 and is "not odd" (even):

! in !ISODD(Element)!ISODD(Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.

The formula fragment is run once for every array element to be tested, and is expected to return TRUE if the element should be considered, and FALSE otherwise. To do its work, it has access to the values Element (the element of the first array currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source, the first array.

Using wildcards in the condition to match text flexibly

When the first array contains text strings, the condition can use special characters to match multiple text strings. This formula returns 55 ((10 + 100) / 2(10 + 100) / 2), because both "car" and "cat" match the wildcard "ca?":

? matches any character. "ca?" matches not only "car" and "cat", but also "cab", "can" and "cap". However, it does not match "card", because ? matches exactly one character, nor does it match "ca".

* matches no character, a single character, or multiple characters. As such, "ca*" matches everything that "ca?" matches, but also "card", "ca" and "Canada" (matching is case-insensitive).

? and * can be combined in the same pattern. Colo*r ad*s are great? matches both "Color ads are great!" and "Colour adverts are great?".

Sometimes, the special characters ? and * need to be included as-is, and not have special meaning. To achieve that, precede each special character with a tilde ("~"). This formula returns 100, because only "ca*" in the first array matches "ca~*":

In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".

Regular expressions are much more powerful than wildcards, but are also harder to learn. Use REGEXMATCH in a formula fragment to use regular expressions with AVERAGEIF.

This formula returns 10, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers).

Refer to the documentation of REGEXMATCH for more information.

Using FILTER, AVERAGE and REDUCE instead of AVERAGEIF

The work AVERAGEIF performs can be divided into two parts: filtering out the unwanted elements from the first array and then returning the average of the elements of the second array which correspond to the ones in the first array which made the cut. As such, these formulas are equivalent:

The version using FILTER provides more flexibility, as the final step (calculating the arithmetic mean through AVERAGE) can be replaced by another function (like SUM, which adds the remaining numbers together), independent of the filtering logic.

For even more flexibility, consider replacing AVERAGE with REDUCE. Refer to the example below for more information.

Examples