HYPGEOM.DIST Function

Returns the probability of a number of successes when sampling without replacement.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice HYPGEOM.DIST in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

HYPGEOM.DIST is for drawing a sample from a small, fixed group without putting items back — inspecting part of a shipment, picking names from a list. Each pick changes the odds of the next one, so BINOM.DIST would be slightly off.

With cumulative FALSE it's exactly sample_s successes in the sample; with TRUE, at most sample_s.

Syntax

=HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) Explain

Arguments

ArgumentRequired?What it does
sample_sYesSuccesses in the sample.
number_sampleYesSize of the sample.
population_sYesSuccesses in the whole population.
number_popYesSize of the population.
cumulativeYesTRUE for the chance of at most this many; FALSE for exactly this many.

Examples

Example 1 — Will inspection catch a faulty unit?

A shipment of 50 units contains 4 faulty ones. The inspector checks 10.

B5fx=HYPGEOM.DIST(0,B4,B3,B2,FALSE)
AB
1MeasureValue
2Shipment50
3Faulty in shipment4
4Units checked10
5Finds none0.3968
6Finds at least one0.6032
=HYPGEOM.DIST(0,B4,B3,B2,FALSE) Explain

There's about a 40% chance the inspection misses every faulty unit.

Example 2 — Exactly two faulty units found

The same shipment and sample.

=HYPGEOM.DIST(2,10,4,50,FALSE) Explain

Returns about 0.152.

Common mistakes

  • Argument order. Sample figures come first, then population figures.
  • #NUM! A count is negative, sample_s is more than number_sample or population_s, or the sample is bigger than the population.

Notes

  • HYPGEOM.DIST replaces the older HYPGEOMDIST function.
  • Related functions: BINOM.DIST, COMBIN.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.