How to Use the HYPGEOMDIST Function in Excel
Today, we’ll explore the HYPGEOMDIST function, a statistical formula used both in Excel and Google Sheets for calculating the probability of achieving a certain number of successes within a sample size from a given population that has a specific number of successes.
How does it work?
The syntax for the HYPGEOMDIST function in Excel and Google Sheets is detailed below:
=HYPGEOMDIST(sample_s, number_sample, population_s, number_pop, cumulative)sample_s: Represents the number of successes in the sample.number_sample: Indicates the total number of observations in the sample.population_s: Specifies the number of successes in the entire population.number_pop: Describes the total population size.cumulative: This logical argument determines the function’s output. If set to TRUE, the function provides the cumulative distribution function; if FALSE, it returns the probability mass function. This parameter is optional.
Examples of tasks where HYPGEOMDIST can be applied:
Example 1: Calculate the likelihood of drawing 2 aces from 5 cards out of a standard 52-card deck, which includes 4 aces.
| sample_s | number_sample | population_s | number_pop | cumulative | Formula | Result |
|---|---|---|---|---|---|---|
| 2 | 5 | 4 | 52 | FALSE | =HYPGEOMDIST(2, 5, 4, 52, FALSE) | 0.1264 |
The probability of drawing 2 aces when selecting 5 cards from a standard deck of 52 cards is approximately 0.1264, or 12.64%.
Example 2: Assess the cumulative probability of obtaining no more than 3 heads when flipping a fair coin 5 times.
| sample_s | number_sample | population_s | number_pop | cumulative | Formula | Result |
|---|---|---|---|---|---|---|
| 0-3 | 5 | 2 | 2 | TRUE | =HYPGEOMDIST(3, 5, 2, 2, TRUE) | 0.9688 |
Thus, the cumulative probability of getting up to 3 heads when flipping a fair coin 5 times is about 0.9688 or 96.88%.
As demonstrated in the examples, the HYPGEOMDIST function can be invaluable for various scenarios where you need to calculate probabilities based on specific samples and populations. Be sure to adjust the input values as necessary for your specific application.
More information: https://support.microsoft.com/en-us/office/hypgeomdist-function-23e37961-2871-4195-9629-d0b2c108a12e