Documentation/Calc Functions/MEDIAN

    From The Document Foundation Wiki

    Function name:

    MEDIAN

    Category:

    Statistical Analysis

    Summary:

    Calculates the median of a set of unsorted data points. The median is the value in the middle of an ordered dataset and it separates the lowest 50% of values from the highest 50%.

    After a set of numbers is arranged in ascending order:

    • If the number of values is odd, the number in the middle of the set is the median.
    • If the number of values is even, then the median is the simple average of the middle two numbers.

    Syntax:

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

    Returns:

    Returns a real number, which is the median of the supplied data points.

    Arguments:

    Number 1, Number 2, … , Number 255 give the set of real numbers from which the median is to be calculated. 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 MEDIAN 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 MEDIAN reports a parameter list error (Err:504).
    • Text in cells and empty cells are ignored.
    • If there are no supplied numbers, then MEDIAN reports a #VALUE! error.
    • If only one value is supplied, then MEDIAN returns that value.

    Additional details:

    • If the number of values (n) in the ordered data set is odd, then the median is given by:
    [math]\displaystyle{ median(Y)\:=\:Y\left[\frac{n}{2}\right] }[/math]
    where Y represents the ordered data set.
    • If the number of values in the ordered data set is even, then the median is given by:
    [math]\displaystyle{ median(Y)\:=\:\frac{ Y\left[ \frac{n-1}{2}\right] + Y\left[ \frac{n+1}{2}\right] }{2} }[/math]
    • For more information on the median value, visit Wikipedia's Median page.

    Examples:

    Formula Description Returns
    =MEDIAN(A1:A5) where cells A1:A5 contain the values 5, 1, 9, 21, and 20 respectively. For an odd number of values, the median will be the number in the middle of the set, when placed in ascending order. 9
    =MEDIAN(5.5; 20; 9.5; 1.3) For an even number of values, the median will be the average of the two values in the middle of the set. 7.5
    =MEDIAN({5, 2, 8, 10, -2}) For an odd number of values, the median will be the number in the middle of the set. 5

    Related LibreOffice functions:

    AVERAGE

    MODE

    MODE.MULT

    MODE.SNGL

    ODF standard:

    Section 6.18.47, part 2

    Related (or similar) Excel functions:

    MEDIAN