Documentation/Calc Functions/HYPGEOM.DIST

    From The Document Foundation Wiki

    Function name:

    HYPGEOM.DIST

    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:

    HYPGEOM.DIST(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, 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 HYPGEOM.DIST reports a #VALUE! error.
    • If any of X, N sample, Successes, or N population is a non-integer value, then HYPGEOM.DIST truncates it to an integer value.
    • HYPGEOM.DIST 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 HYPGEOMDIST the Cumulative argument may be omitted. The requirements for HYPGEOMDIST are specified in ODF 1.2; HYPGEOM.DIST is provided for interoperability with Microsoft Excel.
    • 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
    • The name space for HYPGEOM.DIST is COM.MICROSOFT.HYPGEOM.DIST.
    • For more information on the hypergeometric distribution, visit Wikipedia's Hypergeometric distribution page.

    Examples:

    Formula Description Returns
    =HYPGEOM.DIST(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. 0.325130052020808
    =HYPGEOM.DIST(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 =HYPGEOM.DIST(0; 5; 26; 52; 0)+HYPGEOM.DIST(1; 5; 26; 52; 0)+HYPGEOM.DIST(2; 5; 26; 52; 0)+HYPGEOM.DIST(3; 5; 26; 52; 0) utilizes multiple values from the probability mass function to calculate the same result. 0.825130052020808
    =HYPGEOM.DIST(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:

    HYPGEOMDIST

    ODF standard:

    None

    Related (or similar) Excel functions:

    HYPGEOM.DIST