The Excel HYPGEOM.DIST function returns the hypergeometric distribution — the probability of drawing a given number of successes in a fixed sample drawn without replacement from a finite population.
Syntax
| Argument | Description | |
|---|---|---|
sample_s | Required | The number of successes you want in the sample. |
number_sample | Required | The size of the sample drawn. |
population_s | Required | The total number of successes in the population. |
number_pop | Required | The size of the whole population. |
cumulative | Required | TRUE returns the cumulative probability (sample_s or fewer); FALSE returns the exact probability mass for sample_s. |
How to use it
Use HYPGEOM.DIST for sampling without replacement — cards from a deck, defective parts in a shipment, marked fish in a lake. (When draws are with replacement, use BINOM.DIST instead.)
Read the example as: a population of 20 contains 8 successes; you draw a sample of 4; what is the chance of getting exactly 1 success? Setting cumulative to TRUE sums the probabilities from 0 up to sample_s.
Binomial vs hypergeometric: if the population is very large relative to the sample, draws are nearly independent and the binomial distribution is a good approximation. For small finite populations, the hypergeometric is exact.
Try it: interactive demo
Pick a HYPGEOM.DIST example to see the formula and its result.
Practice workbook
Frequently asked questions
What is the difference between HYPGEOM.DIST and BINOM.DIST?
What is the order of the arguments?
sample_s, number_sample, population_s, number_pop, cumulative — successes in the sample, sample size, successes in the population, population size, then the cumulative flag.How is HYPGEOM.DIST different from the legacy HYPGEOMDIST?
HYPGEOMDIST (no dot) had no cumulative argument — it only returned the exact probability mass. HYPGEOM.DIST adds the cumulative flag so you can get cumulative probabilities too. HYPGEOMDIST still works for backward compatibility.Why might HYPGEOM.DIST return an error?
#NUM! if any argument is out of range — for example sample_s greater than number_sample, or population_s greater than number_pop. Non-numeric arguments return #VALUE!.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class