HYPGEOM.DIST function
HYPGEOM.DIST returns the chance of drawing a given number of successes when sampling without replacement, such as defective parts drawn from a small batch. Counts are truncated to whole numbers.
Example
Read it as a sentence: in a population of 20 with 8 successes, draw a sample of 4 and find the chance of exactly 1 success, which gives 0.363261094.
A
B
C
D
1
0.363261094
2
3
Syntax
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
-
sample_s(required) - Successes in the sample.
-
number_sample(required) - Sample size.
-
population_s(required) - Successes in the population.
-
number_pop(required) - Population size.
-
cumulative(required) - TRUE for at most sample_s successes, FALSE for exactly.
Returns: The hypergeometric probability of the sample result.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=HYPGEOM.DIST(1,4,8,20,FALSE) | 0.363261094 | Working example |
=HYPGEOM.DIST(1,4,8,20,TRUE) | 0.465428277 | Other cumulative flag |
=HYPGEOM.DIST(1,4,8,20,0) | 0.363261094 | Cumulative as 0 |
=HYPGEOM.DIST("1",4,8,20,FALSE) | 0.363261094 | Text-number in first arg |
=HYPGEOM.DIST({1,1},4,8,20,FALSE) | 0.363261094 | Array in first arg (lifts, spills) |
=HYPGEOM.DIST(5,4,8,20,FALSE) | 0 | sample_s > number_sample |
=HYPGEOM.DIST("abc",4,8,20,FALSE) | #VALUE! | Non-numeric text in first arg |
=HYPGEOM.DIST(NA(),4,8,20,FALSE) | #N/A | Error in first arg |
Watch out for
- Sampling is without replacement; otherwise use BINOM.DIST.
- All counts are truncated to integers.
Where it works
- Excel
- HYPGEOM.DIST arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, but behaves differently. Sheets also has HYPGEOMDIST with fewer arguments, so check the cumulative option.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .