Documentation/Calc Functions/DEVSQ

    From The Document Foundation Wiki

    Function name:

    DEVSQ

    Category:

    Statistical Analysis

    Summary:

    Calculates the sum of the squared deviations from the mean of the specified data set.

    Syntax:

    DEVSQ(Number 1 [; Number 2 [; … [; Number 255]]])

    Returns:

    Returns a non-negative real number, which is the sum of the squared deviations from the mean of the specified data set.

    Arguments:

    Number 1, Number 2, ..., Number 255 give the data set containing the values to contribute to the calculation. Each argument may be a value or an expression, a reference to a single cell, a reference to a cell range, the name of a named or database range, or an inline array. Note that although DEVSQ can accept up to 255 arguments, each argument could specify a range of cells; this means that the number of real numbers processed could be many more than 255.

    • If any argument is a text string in quotation marks, then DEVSQ reports a parameter list error (Err:504).
    • DEVSQ includes numbers, dates/times, and logical values in its calculation.
    • DEVSQ ignores empty cells and text strings in cells.
    • If there are no supplied numbers, then DEVSQ reports a #DIV/0! error.
    • If only one value is supplied, then DEVSQ returns 0.

    Additional details:

    • DEVSQ ignores any text in cells or empty cells. If you suspect wrong results, look for text within the supplied data range(s). To highlight text content within a data range, use Calc's View ▸ Value Highlighting feature.
    • The formula for DEVSQ is:
    [math]\displaystyle{ \text{DEVSQ}~=~\sum_{i=1}^{n}\left ( x_{i}\:-\:\bar{x} \right )^2 }[/math]
    where
    • xi are the values in the data set
    • is the mean of the values in the data set
    • n is the number of values in the data set.
    • The value returned by DEVSQ is related to the variance of the data set, except that the value returned by DEVSQ has not been divided by n (population variance) or n-1 (sample variance).

    Examples:

    Formula Description Returns
    =DEVSQ(A1:A5) where cells A1:A5 contain the values 1, 2, 3, 4, and 5 respectively. Here the function calculates the sum of the squared deviations from the mean for the five integer numbers given The same result is obtained from the formulas =DEVSQ(A1; A2; A3; A4; A5) and =DEVSQ({1, 2, 3}; {4, 5}) 10
    =DEVSQ(1.5; -2.0; 0.0; -4.2) Here the function calculates the sum of the squared deviations from the mean for the four real numbers given. 18.3675

    Related LibreOffice functions:

    VAR

    VARA

    VARP

    VARPA

    ODF standard:

    Section 6.18.20, part 2

    Related (or similar) Excel functions:

    DEVSQ