Documentation/Calc Functions/FREQUENCY

    From The Document Foundation Wiki
    Other languages:

    Function name:

    FREQUENCY

    Category:

    Array

    Summary:

    Determines the frequency distribution of the data in an input array. FREQUENCY counts the number of values in the input array that are within specified intervals and returns a single-column array containing these counts.

    Syntax:

    FREQUENCY(Data; Classes)

    Returns:

    Returns a single-column array of integer values containing the count for each defined interval. The length of this array is one greater than the number of values specified in the second argument.

    Arguments:

    Data is a reference list specifying the range(s) of cells containing the data for which the frequency distribution is required. These data values need not be ordered. Data takes one of the following forms:

    • A single cell range, defined by entering the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference (for example, A1:E10).
    • Multiple cell ranges can be passed as a single argument by providing them in a separated list, surrounded by parentheses – for example, =FREQUENCY((A1:B6; C2:D7; E3:F8); …). In this case Calc will reformat your function call to use the tilde reference concatenation operator – for example =FREQUENCY((A1:B6~C2:D7~E3:F8); …). Alternatively, you can use the tilde reference concatenation operator directly without parentheses – for example =FREQUENCY(A1:B6~C2:D7~E3:F8; …).
    • A single cell range referenced by the name of a database range (defined by selecting Data ▸ Define Range on the Menu bar).
    • One or more cell ranges referenced by the name of a named range. Named ranges defined using Sheet ▸ Named Ranges and Expressions ▸ Define on the Menu bar can be configured to comprise multiple areas by using the tilde reference concatenation operator in the Range or formula expression field of the Define Name dialog.
    • An inline array (for example, {1, 2, 3; 4, 5, 6}).

    Classes is a reference list specifying the intervals for which the frequency distribution is required. It takes one of the same forms as listed above for the Data argument. The following notes provide further information:

    • While not essential, it is recommended that the values in Classes be organised as a single-column array of cells, vertically aligned with the cells used for the output array. This makes is easier for the user to understand the results from FREQUENCY.
    • It is recommended that the values in Classes be sorted in ascending order, making the outputs easier to understand. FREQUENCY will not report an error if the values are unordered, but the user may have more difficulty interpreting the returned array. The values in the output array are ordered to match the original order of Classes.
    • Assuming that the values in Classes are ordered as suggested:
    • The first element in the returned array is the count of values in Data that are less than or equal to the first value in Classes.
    • The second value in the returned array is the count of values in Data that are greater than the first value in Classes but less than or equal to the second value in Classes.
    • … and so on.
    • The returned array is one entry longer than Classes, with the extra element containing the count of values in Data that are greater than the last value in Classes.

    The following conditions may arise:

    • If the cells within Data contain no numbers, then FREQUENCY reports a #VALUE! error.
    • When determining the frequency distribution of the numbers in Data, FREQUENCY ignores any cell that is empty or contains a text string. The logical values TRUE and FALSE are counted as 1 and 0 respectively. Dates and times are treated as numbers, using date-time serial numbers.
    • If the cells within Classes contain no numbers, then FREQUENCY reports a #VALUE! error.
    • In Classes, FREQUENCY ignores any cell that is empty or contains a text string. The logical values TRUE and FALSE are counted as 1 and 0 respectively. Dates and times are treated as numbers, using date-time serial numbers.

    Additional details:

    Details specific to FREQUENCY function

    • This function ignores any text or empty cells within a data range. If you suspect wrong results from FREQUENCY, look for text in the data ranges. To highlight text contents in a data range, use Calc’s Value Highlighting feature.


    General information about Calc's array functions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing Calc's array functions.

    To avoid repetition within the subsequent text, only Windows/Linux key combinations are specified. The following table shows the mapping between these key combinations and their macOS equivalents.

    Mapping between relevant Windows/Linux and macOS key combinations
    Windows/Linux key combination macOS equivalent Comments
    Ctrl + ⇧ Shift + ↵ Enter ⌘ Cmd + ⇧ Shift + ↵ Enter Create an array formula.
    Ctrl + / ⌘ Cmd + / Select all cells in an array formula range. "/" is the division key on the numeric keypad.
    Ctrl + C ⌘ Cmd + C Copy.
    Ctrl + V ⌘ Cmd + V Paste.
    • An array is a rectangular range of cells containing data. For example, a square range of three rows by three columns is a 3 x 3 array. The smallest possible array is a 1 x 2 or 2 x 1 array, with two adjacent cells.
    • A formula in which the individual values in a cell range are evaluated is referred to as an array formula. The difference between an array formula and other formulas is that the array formula deals with several values simultaneously instead of just one.
    • If you create an array formula using the Function Wizard, you must mark the Array check box so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.
    • If you enter the array formula directly into the cell, you must use the key combination Ctrl + ⇧ Shift + ↵ Enter instead of just the ↵ Enter key. Only then does the formula become an array formula.
    • An array formula is displayed inside curly brackets or braces ("{" and "}"). It is not possible to create an array formula by manually entering these braces.
    • Some array functions take parameters that are forced to evaluate as an array formula, even when the formula is entered normally. These are MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, and SUMXMY2.
    • Calc supports constant inline arrays in formulas. An inline array is enclosed within curly brackets or braces ("{" and "}"). Individual elements can be numbers (including negatives), logical constants (TRUE, FALSE), or literal strings. Non-constant expressions are not allowed. Arrays can be entered with one or more rows, and one or more columns. All rows must comprise the same number of elements; similarly, all columns must comprise the same number of elements.
    • Editing array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line, edit the formula as required, and use the key combination Ctrl + ⇧ Shift + ↵ Enter.
    • Deleting array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Press Delete to delete the array contents, including the array formula. Alternatively, press ← Backspace to bring up the Delete Contents dialog box, select Formulas or Delete all, and click OK.
    • Copying array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line. Copy the formula by pressing Ctrl + C. Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the Input line. Paste the formula by pressing Ctrl + V in the selected space and confirm it using the key combination Ctrl + ⇧ Shift + ↵ Enter. The selected range now contains the array formula.
    • Adjusting an array range. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Below the selection, to the right, you will see a small icon with which you can zoom in or out on the range using your mouse. When you adjust the array range, the array formula will not be adjusted automatically - you are only changing the range in which the results will appear.

    Examples:

    For these examples, the input data is assumed to be as shown in the following table.

    Sample data for FREQUENCY function
    A B C D E F G
    1 Student Score (%) Band Min (%) Max (%) No. of
    students
    2 Abigail 65 E 0 25 0
    3 Adele 59 D 26 36 1
    4 Clifford 41 C 37 47 6
    5 Codie 49 B 48 58 3
    6 Halima 43 A 59 70 4
    7 Irene 58 A+ 71+ 1
    8 Jack 45
    9 Jordi 64
    10 Katlyn 59
    11 Kyran 44
    12 Monica 47
    13 Raja 38
    14 Rory 30
    15 Saba 73
    16 Skylar 50

    In the above table, the cells with a light blue background are included for information only – they clarify the data displayed but are not processed in the examples of the FREQUENCY function given below. Fifteen students sat an examination and their names and percentage test scores are listed in cells A2:A16 and B2:B16 respectively. Students are assigned to a grade band (listed in cells D2:D7) based on where their percentage test score lies in the intervals specified in cells E2:F7 – students scoring 25% or less are in band E; students with a score in the range [26, 36] are in band D; and so on. The objective of the examples is to calculate the number of students falling within each grade band (cells G2:G7).

    Formula Description Returns
    {=FREQUENCY(B$2:B$16; F$2:F$6)} entered as an array formula after selecting cell G2. Here the function returns the values 0, 1, 6, 3, 4, and 1 in cells G2:G7 respectively.
    • The value 0 in cell G2 indicates that no student scored in the range [0, 25].
    • The value 1 in cell G3 indicates that one student (Rory) scored in the range [26, 36].
    • The value 6 in cell G4 indicates that six students (Clifford, Halima, Jack, Kyran, Monica, and Raja) scored in the range [37, 47].
    • The value 3 in cell G5 indicates that three students (Codie, Irene, and Skylar) scored in the range [48, 58].
    • The value 4 in cell G6 indicates that four students (Abigail, Adele, Jordi, and Katlyn) scored in the range [59, 70].
    • The value 1 in cell G7 indicates that one student (Saba) scored greater than 70.

    Notice that the sum of the values in cells G2:G7 is 15, equal to the number of students whose test scores are included.

    See Description
    {=FREQUENCY(test_scores; upper_values)} entered as an array formula after selecting cell G2. test_scores is a named range covering the cells B2:B16 and upper_values is a named range covering F2:F6. Here the function returns the values 0, 1, 6, 3, 4, and 1 in cells G2:G7 respectively, exactly the same as the previous example. See Description
    {=FREQUENCY({65, 59, 41; 49, 43, 58; 45, 64, 59; 44, 47, 38; 30, 73, 50}; {25, 36, 47, 58, 70})} entered as an array formula after selecting cell G2. Here the function returns the values 0, 1, 6, 3, 4, and 1 in cells G2:G7 respectively, exactly the same as the previous examples. Notice that the first argument contains the same test scores as the previous examples but arbitrarily arranged in a 5 row x 3 column inline array rather than the single-column cell array used in the previous examples. Also notice that the second argument contains the same upper band limits as the previous examples but arranged in a single-row inline array rather than a single-column cell array. See Description

    Related LibreOffice functions:

    None.

    ODF standard:

    Section 6.18.29, part 2

    Related (or similar) Excel functions:

    FREQUENCY