Documentation/Calc Functions/AVEDEV

    From The Document Foundation Wiki

    Function name:

    AVEDEV

    Category:

    Statistical Analysis

    Summary:

    Calculates the average of the absolute deviations of a set of data points from their mean. The function provides a measure of statistical dispersion and thus gives an indication of the spread of the values in the data set.

    Syntax:

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

    Returns:

    Returns a non-negative real number, which is the average of the absolute deviations of the supplied data points from their arithmetic mean.

    Arguments:

    Number 1, Number 2, … , Number 255 give the set of real numbers for which the average of the absolute deviations is to be calculated. Each argument may take one of the following forms:

    • A real number, or an expression that evaluates to a real number.
    • A reference to a single cell containing a real number.
    • A simple reference to a cell range containing real numbers (for example, A1:B9).
    • The name of a named range, comprising cells containing real numbers.
    • The name of a database range, comprising cells containing real numbers.
    • An inline array of real numbers (for example, {1.1, 2.2, 3.3, 4.4}).

    Note that although AVEDEV 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.

    The following conditions (including errors) may be encountered:

    • If any argument is a string in quotation marks, then AVEDEV reports a parameter list error (Err:504).
    • Text in cells and empty cells are ignored.
    • If there are no supplied numbers, then AVEDEV reports a #NUM! error.
    • If only one value is supplied, then AVEDEV returns the value 0.

    Additional details:

    • The formula for AVEDEV is:
    [math]\displaystyle{ \text{AVEDEV} ~=~ \frac{1}{n}\:\sum_{i=1}^{n}\left| (x_i\:-\:\overline{x}) \right| }[/math]
    where [math]\displaystyle{ x_i }[/math] is the i-th value, [math]\displaystyle{ n }[/math] is the total number of values, and [math]\displaystyle{ \overline{x} }[/math] is the arithmetic mean of the values.
    • AVEDEV benefits from its use of a Kahan summation algorithm which attempts to reduce the numerical error that can arise when adding a sequence of finite-precision floating-point numbers.

    Examples:

    Formula Description Returns
    =AVEDEV(A1:A5) where cells A1:A5 contain the values 1, 2, 3, 4, and 5 respectively. Here the function calculates the average of the absolute deviations as 1.2. The same result is obtained from the formulas =AVEDEV(A1; A2; A3; A4; A5) and =AVEDEV({1, 2, 3}; {4, 5}) 1.2
    =AVEDEV(1.5; -2.0; 0.0; -4.2) Here the function calculates the average of the absolute deviations as 1.925. 1.925

    Related LibreOffice functions:

    AVERAGE

    SUM

    ODF standard:

    Section 6.18.2, part 2

    Related (or similar) Excel functions:

    AVEDEV