Documentation/Calc Functions/AVERAGEIF

    From The Document Foundation Wiki

    Function name:

    AVERAGEIF

    Category:

    Statistical Analysis

    Summary:

    In its simplest form, AVERAGEIF determines which cells in a range satisfy a specified criterion and calculates the average (arithmetic mean) of the numbers in those matched cells. In its second form, AVERAGEIF determines which cells in one range satisfy a specified criterion and then calculates the average of the numbers in the corresponding cells of a second range.

    The criterion passed to AVERAGEIF can utilize either wildcards or regular expressions.

    Syntax:

    AVERAGEIF(Range; Criterion [; Average range ])

    Returns:

    Returns a real number that is the result of averaging the numbers in relevant cells.

    Arguments:

    Range Argument

    Range specifies the cells to be matched and, when the Average range argument is omitted, the cells to be averaged. Range usually takes one of the following forms:

    • A reference to a cell range. If Average range is omitted, then Range may utilize the reference concatenation operator (~).
    • The name of a named range.
    • The name of a database range.

    Criterion argument

    Criterion is the criterion for matching, or a cell containing that criterion. Criterion can take one of the following forms:

    • A number. Dates and logical (boolean) values are treated as numbers.
    • An expression, such as 2/3, SQRT($D$1), or DATE(2021; 11; 1).
    • A text string, such as "golf" or "<>10".

    AVERAGEIF looks for cells in Range that are equal to Criterion, unless Criterion is a text string that starts with a comparison operator (>, <, >=, <=, =, or <>). In the latter case AVERAGEIF compares the cells in Range with the remainder of the text string (interpreted as a number if possible; and as text otherwise).

    For example,

    • the condition ">4" tests if the content of each cell is greater than the number 4,
    • the condition "<dog" tests if the content of each cell comes alphabetically before the text "dog",
    • and the condition "<>2021-11-01" tests if the content of each cell is not equal to the specified date.

    Criterion supports the following specific behaviors:

    • The string "=" matches empty cells. For example, the formula =AVERAGEIF(B1:B10; "="; A1:A10)returns the average of all values in the range A1:A10 if all cells in the range B1:B10 are empty. Note that "=0" does not match empty cells.
    • The string "<>" matches non-empty cells. For example, the formula =AVERAGEIF(C1:C10; "<>"; A1:A10)returns the average of all values in the range A1:A10 if there are no empty cells in the range C1:C10.
    • If the value after the "<>" comparison operator is not empty, then Criterion matches any cell content except that value, including empty cells.

    Average range argument

    Average range specifies the cells to be averaged, and takes one of the forms listed for Range. Average range should normally have the same dimensions as Range. If this is not the case, AVERAGEIF will average a range of cells starting at the first cell in Average range and having the same dimensions as Range.

    Error conditions

    • If the Average range argument is provided and the reference concatenation operator is utilized within Range, then AVERAGEIF reports a parameter list error (Err:504).
    • If no cell satisfies the specified Criterion, then AVERAGEIF reports a #DIV/0! error.
    • If no cell that satisfies the specified Criterion contains a number, then AVERAGEIF reports a #DIV/0! error.

    Additional details:

    Details specific to AVERAGEIF function

    • AVERAGEIF allows only one matching criterion. If you need to average cells based on multiple criteria, use the AVERAGEIFS function.
    • The default matching performed by AVERAGEIF is case-insensitive. However, a case-sensitive match can be carried out when using a regular expression by including a "(?-i)" mode modifier within the regular expression, as demonstrated by one of the examples below.
    • The behavior of AVERAGEIF is affected by several settings available on the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS).
    • If the checkbox is ticked for Search criteria = and <> must apply to whole cells, then the condition "red" will match only "red"; if unticked it will match "red", "Fred", "red herring".
    • If the checkbox is ticked for Enable wildcards in formulas, the condition will match using wildcards – so for example "b?g" will match "bag", "beg", "big", "bog", and "bug".
    • If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions – so for example "r.d" will match "red", "rid", and "rod", while "red.*" will match "red", "redraw", and "redden".
    • The setting of the Case sensitive checkbox has no impact on the operation of AVERAGEIF.

    General information about Calc's regular expressions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing functions that manipulate regular expressions.

    • A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
    • Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU), see their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
    • In addition, the LibreOffice Help system provides a high-level list of regular expressions.
    • Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.

    General information about Calc's wildcards

    Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.

    Calc supports either wildcards or regular expressions as arguments depending on the current application settings. By default, wildcards are supported instead of regular expressions.

    To make sure wildcards are supported, go to Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate and check if the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.

    The following table identifies the wildcards that Calc supports.

    Calc wildcards
    Wildcard Description
    ? (question mark) Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug".

    Note that it will not match "bg" as well, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match.
    * (asterisk) Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings.

    If the option Search criteria = and <> must apply to whole cells is disabled in Tools > Options > LibreOffice Calc > Calculate, then "forecaster" will be a match using the "*cast" search string.
    ~ (tilde) Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character.

    For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s".

    Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".

    These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.

    Examples:

    Basic examples

    Data for Basic examples
    A B C D E F
    1 −1 apples 12 >=0
    2 2 7 4 pears 19 10
    3 −3 9 5 pears 10
    4 4 11 6 apples 27
    5 −5 apples 97
    6 6 apples 73
    7 −7 pears 79
    8 8 pears 71
    9 −9 apples 40
    A B C D E F
    Formula Description Returns
    =AVERAGEIF(A1:A9; "<0") Average the negative numbers in the cell range A1:A9. -5
    =AVERAGEIF(A1:A9; F1) Average the non-negative numbers in the cell range A1:A9. 5
    =AVERAGEIF(B2:B4; "<"&F2; C2:C4) The values in cells B2 and B3 are less than 10, so the average is based on the corresponding values in cells C2 and C3. 4.5
    =AVERAGEIF(D1:D9; "apples"; E1:E9) Average quantity of apples. 49.8

    Other examples

    The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.

    Database of sales data for use in AVERAGEIF examples
    A B C D E F
    1 Date [ISO 8601] Sales Value Category Region Employee
    2 2021-10-02 $ 1508 Golf East Hans
    3 2021-10-02 $ 0410 Tennis North Kurt
    4 2021-10-02 $ 2340 Sailing South Ute
    5 2021-10-03 $ 4872 Tennis East Brigitte
    6 2021-10-06 $ 3821 Tennis South Fritz
    7 2021-10-06 $ 2623 Tennis East Fritz
    8 2021-10-07 $ 3739 Golf South Fritz
    9 2021-10-08 $ 4195 Golf West Ute
    10 2021-10-10 $ 2023 Golf East Hans >=south
    A B C D E F
    Formula Description Returns
    =AVERAGEIF(B2:B10; ">=4000") Average the numeric entries in the Sales Value data that are greater than or equal to 4000. 4,533.5
    =AVERAGEIF(E2:E10; "ute"; B2:B10) For the Employee named Ute, average the corresponding numeric entries in the Sales Value data. (case-insensitive). 3,267.5
    =AVERAGEIF(CategoryData; "golf"; B2:B10)

    where the named range CategoryData has been created to cover the cell range C2:C10.

    For the Category named Golf, average the corresponding numeric entries in the Sales Value data. 2,866.25
    =AVERAGEIF(D2:D10; F10; B2:B10) Locate the entries for South and West in the Region data, and average the corresponding numeric entries in the Sales Value data. 3,523.75
    =AVERAGEIF(A2:A10; DATE(2021; 10; 2); B2:B10) Locate the entries for 2021-10-02 in the Date column, and average the corresponding numeric entries in the Sales Value data. 1,419.33...
    =AVERAGEIF(A2:A10; ">="&DATE(2021; 10; 7); B2:B10) Locate the entries dated on or after 2021-10-07 in the Date column, and average the corresponding numeric entries in the Sales Value data. 3,319
    =AVERAGEIF(D2:D10; "????"; B2:B10) With wildcards enabled, locate the four-character entries in the Region data (East and West), and average the corresponding numeric entries in the Sales Value data. 3,044.2
    =AVERAGEIF(E2:E10; "^f.*"; B2:B10) With regular expressions enabled, locate the entries in the Employee data that start with the letter "f" or "F" (Fritz), and average the corresponding numeric entries in the Sales Value data. 3,394.33...
    =AVERAGEIF(E2:E10; "(?-i)ute"; B2:B10)

    With regular expressions enabled and with the "(?-i)" mode modifier, the lookup is case-sensitive, and no entries are found in the Employee data. Contrast this with the second example in this table.

    #DIV/0!

    Related LibreOffice functions:

    AVERAGE

    AVERAGEA

    AVERAGEIFS

    COUNTIF

    DATE

    SUMIF

    ODF standard:

    Section 6.18.5, part 2

    Related (or similar) Excel functions:

    AVERAGEIF