Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .