The HYPGEOM.DIST function in Google Sheets is a powerful statistical tool used for calculating the hypergeometric distribution. This function is particularly useful when analyzing scenarios where objects are drawn from a finite population without replacement, allowing users to determine probabilities in a variety of contexts, including quality control, genetics, and resource allocation.

Syntax

HYPGEOM.DIST(x, n, M, N, cumulative)

• x: The number of successes in the sample.
• n: The sample size drawn from the population.
• M: The number of successes in the population.
• N: The population size.
• cumulative: A boolean value indicating whether to return the cumulative distribution function (TRUE) or the probability mass function (FALSE).

Example #1

HYPGEOM.DIST(5, 10, 20, 30, FALSE)

This function call calculates the probability of getting exactly 5 successes out of 10 draws from a population of 30, where 20 of those are successful. The result would be, for example, 0.1652.

Example #2

HYPGEOM.DIST(8, 10, 20, 30, TRUE)

Here, this function evaluates the cumulative probability of obtaining 8 or fewer successes in the same scenario. The output might be around 0.7381, indicating the cumulative likelihood of accumulating that many successes.

Example #3

HYPGEOM.DIST(3, 7, 15, 40, FALSE)

In this example, the function computes the probability of achieving exactly 3 successes when drawing 7 items from a population of 40, where 15 are considered successful. The result could be approximately 0.1967.

Error handling

• NUM!: Occurs if any argument is non-numeric, such as letters or symbols in place of numbers.
• VALUE!: Appears when the arguments are of the wrong type, for example, if cumulative is not a boolean value.
• REF!: Indicates that one of the arguments refers to a cell that does not exist or is invalid.

Conclusion

In summary, the HYPGEOM.DIST function is an essential tool in Google Sheets for statistical analysis involving hypergeometric distribution scenarios. By understanding its syntax and how to apply it effectively through various examples, users can leverage this function for making informed decisions based on probability assessments.