Documentation/Calc Functions/HYPGEOMDIST

    From The Document Foundation Wiki

    Function name:

    HYPGEOMDIST

    Category:

    Statistical Analysis

    Summary:

    Calculates hypergeometric distribution probabilities from either the probability mass function or the cumulative distribution function. The hypergeometric distribution is a discrete probability distribution that describes the probability of a particular number of successes in a sample drawn from a finite population without replacement.

    Syntax:

    HYPGEOMDIST(X; N sample; Successes; N population [; Cumulative])

    Returns:

    Returns a real number in the range [0, 1], which is the hypergeometric distribution probability for the given arguments.

    Arguments:

    X is a non-negative integer, or a reference to a cell containing that integer, that is the number of successes achieved in the random sample.

    N sample is a non-negative integer, or a reference to a cell containing that integer, that is the size of the random sample.

    Successes is a non-negative integer, or a reference to a cell containing that integer, that is the number of successes achieved in the total population.

    N population is a non-negative integer, or a reference to a cell containing that integer, that is the size of the total population.

    Cumulative is a logical value, or a reference to a cell containing that value, that determines whether the required probability is taken from the probability mass function or the cumulative distribution function. If Cumulative is set to 0 or FALSE, or if omitted, a value from the probability mass function is calculated. For any other values of Cumulative, a value from the cumulative distribution function is calculated.

    • If any of X, N sample, Successes, or N population is non-numeric, then HYPGEOMDIST reports a #VALUE! error.
    • If any of X, N sample, Successes, or N population is a non-integer value, then HYPGEOMDIST truncates it to an integer value.
    • HYPGEOMDIST reports an invalid argument error (Err:502) if any of the following conditions arise after any truncation.
      • X < 0.0 (the number of sample successes cannot be negative)
      • N sample < X (the number of sample successes cannot exceed the sample size)
      • N population < N sample (the sample size cannot exceed the population size)
      • N population < Successes (the number of population successes cannot exceed the population size)
      • Successes < 0.0 (the number of population successes cannot be negative)

    Additional details:

    • Calc's HYPGEOMDIST and HYPGEOM.DIST functions perform similar calculations. However, there are minor differences between the two functions with respect to their arguments – for HYPGEOM.DIST the Cumulative argument cannot be omitted. The requirements for HYPGEOMDIST are specified in ODF 1.2; HYPGEOM.DIST is provided for interoperability with Microsoft Excel.
    • Note that Microsoft Excel's HYPGEOMDIST function does not support the Cumulative argument and returns only values from the probability mass function.
    • The formula for the probability mass function of the hypergeometric distribution is:
    [math]\displaystyle{ PMF_{hypergeometric}~=~\frac{\binom{M}{x} \binom{N\:-\:M}{n\:-\:x}}{\binom{N}{n}} }[/math]
    where:
    x is the number of successes in n trials
    n is the number of trials
    M is the number of successes in the population
    N is the total population
    The terms in this formula use the standard notation for binomial coefficients, [math]\displaystyle{ \binom{a}{b} }[/math]. Binomial coefficients are set to 0 for [math]\displaystyle{ b \gt a }[/math].
    • Using the same notation, the formula for the cumulative distribution function of the hypergeometric distribution is:
    [math]\displaystyle{ CDF_{hypergeometric}~=~\sum_{i=0}^{x }\frac{\binom{M}{i} \binom{N\:-\:M}{n\:-\:i}}{\binom{N}{n}} }[/math]
    • The following figure shows three sample probability mass function plots for the hypergeometric distribution. Note that the dashed lines are included only to improve the appearance of the plots and do not imply probability mass values for non-integer values along the Sample successes axis.
    Hypergeometric distribution PMF plots.png
    • The following figure shows three sample cumulative distribution function plots for the hypergeometric distribution. Note that the dashed lines are included only to improve the appearance of the plots and do not imply probability mass values for non-integer values along the Sample successes axis.
    Hypergeometric distribution CDF plots.png

    Examples:

    Formula Description Returns
    =HYPGEOMDIST(A1; A2; A3; A4; A5) where cells A1:A5 contain the values 3, 5, 26, 52 and 0 respectively. Here the function calculates the probability of getting exactly three black cards (clubs or spades) when five cards are randomly selected without replacement from a standard pack of 52 playing cards. A value from the probability mass function is returned when the Cumulative argument is set to zero. The formula =HYPGEOMDIST(A1; A2; A3; A4) returns the same value because the default value of 0 is assumed for the omitted Cumulative argument. 0.325130052020808
    =HYPGEOMDIST(3; 5; 26; 52; 1) Here the function calculates the probability of getting three or less black cards (clubs or spades) when five cards are randomly selected without replacement from a standard pack of 52 playing cards. Note that the more verbose formula =HYPGEOMDIST(0; 5; 26; 52; 0)+HYPGEOMDIST(1; 5; 26; 52; 0)+HYPGEOMDIST(2; 5; 26; 52; 0)+HYPGEOMDIST(3; 5; 26; 52; 0) utilizes multiple values from the probability mass function to calculate the same result. 0.825130052020808
    =HYPGEOMDIST(15; 30; 90; 150; TRUE) Here the function calculates a value from the cumulative distribution function of the hypergeometric distribution for the given arguments. 0.148888209892056

    Related LibreOffice functions:

    HYPGEOM.DIST

    ODF standard:

    Section 6.18.37, part 2

    Related (or similar) Excel functions:

    HYPGEOMDIST