HYPGEOMDIST function
HYPGEOMDIST returns the chance of drawing exactly a given number of successes when sampling without replacement. It is a compatibility function; HYPGEOM.DIST replaces it in new workbooks.
Example
Read it as a sentence: in a population of 20 with 8 successes, draw a sample of 4 and find the chance of getting the number of successes in D1, which is empty and counted as 0.
Syntax
HYPGEOMDIST(sample_s, number_sample, population_s, number_pop)
-
sample_s(required) - Successes in the sample.
-
number_sample(required) - Size of the sample.
-
population_s(required) - Successes in the whole population.
-
number_pop(required) - Size of the population.
Returns: Probability of exactly sample_s successes when drawing without replacement.
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 |
|---|---|---|
=HYPGEOMDIST(1,4,8,20) | 0.363261094 | Working example |
=HYPGEOMDIST("1",4,8,20) | 0.363261094 | Text-number in first arg |
=HYPGEOMDIST({1,1},4,8,20) | 0.363261094 | Array in first arg (lifts, spills) |
=HYPGEOMDIST(5,4,8,20) | 0 | sample_s > number_sample |
=HYPGEOMDIST(Data!D1,4,8,20) | 0.102167183 | Blank cell as first arg |
=HYPGEOMDIST("abc",4,8,20) | #VALUE! | Non-numeric text in first arg |
=HYPGEOMDIST(NA(),4,8,20) | #N/A | Error in first arg |
=HYPGEOMDIST(1,25,8,20) | #NUM! | number_sample > population |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Superseded by HYPGEOM.DIST; use that in new workbooks.
- The old form is exact-count only; HYPGEOM.DIST adds a cumulative option.
Where it works
- Excel
- HYPGEOMDIST is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .