How to use HYPGEOMDIST function in Excel?

The `HYPGEOMDIST` function in Excel is used to calculate the hypergeometric distribution, which is useful in scenarios where you want to determine the probability of a certain number of successes in a fixed number of draws, without replacement, from a finite population.

Here’s a step-by-step guide on how to use the `HYPGEOMDIST` function:

Syntax

HYPGEOMDIST(sample_s, number_sample, population_s, number_population)

Parameters

  • sample_s: The number of successes in the sample. This is the number of successful draws you’re interested in.
  • number_sample: The number of draws. This is the total number of items drawn from the population.
  • population_s: The number of successes in the population. This is the total number of successful items available in the population.
  • number_population: The total number of items in the population.

Example

Imagine you have an urn containing 20 balls: 7 red (successes) and 13 white (failures). You want to determine the probability of drawing exactly 3 red balls in 5 draws.

Here’s how you would set this up in Excel:

  • sample_s (number of successes in the sample): 3
  • number_sample (number of draws): 5
  • population_s (number of successful items in the population): 7
  • number_population (total number of items in the population): 20

In Excel, you would enter the formula as follows:

=HYPGEOMDIST(3, 5, 7, 20)

Notes

  • The `HYPGEOMDIST` function will return a probability value between 0 and 1.
  • Ensure that your population parameters (`population_s` and `number_population`) reflect the actual scenario you’re analyzing, as incorrect values will lead to incorrect probability results.
  • Excel also provides `HYPGEOM.DIST` in newer versions, which has a similar functionality but allows you to specify if you want a cumulative distribution.

By using the `HYPGEOMDIST` function, you can accurately calculate probabilities in scenarios involving sampling without replacement, which is common in quality control, ecology, and other fields where resources or items are limited and can’t be replaced.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project