HYPGEOM.DIST in Excel: Count Successes in Sampling Without Replacement

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

All arguments are required. sample_s counts successes in the sample, number_sample is sample size, population_s counts successes in the entire population, and number_pop is population size. TRUE selects cumulative probability; FALSE selects an exact-count probability mass.

Microsoft describes finite sampling without replacement, with each subset of the stated sample size equally likely. “Success” simply labels the category being counted. It does not need to be a desirable outcome.

The count roles are distinct. Swapping sample size and population success count can obscure the intended model even when an accidental numerical result looks plausible.

Put 1, 4, 8 and 20 in B2:B5. Enter =HYPGEOM.DIST(B2,B3,B4,B5,FALSE) in B7. The independent target is 0.3632610939.

There are COMBIN(20,4)=4845 equally likely unordered samples. Exactly one marked item can be selected in COMBIN(8,1)=8 ways, and three unmarked items in COMBIN(12,3)=220 ways. Thus the probability is 1760/4845.

With TRUE, the independent result is 0.4654282766, including zero as well as one marked item. These reproduce Microsoft’s rounded 0.3633 and 0.4654 values.

List possible success counts 0 through 4 in D2:D6. In E2 enter =HYPGEOM.DIST(D2,4,8,20,FALSE) and fill down.

Independent masses are approximately 0.1021671827, 0.3632610939, 0.3814241486, 0.1386996904 and 0.0144478844. Their sum is one. The weighted count sum is 1.6, agreeing with sample_size*population_successes/population_size.

The expected count need not be an attainable integer outcome. It summarizes repeated draws under the stated model rather than promising 1.6 marked items in one actual sample.

For at least two marked items, use =1-HYPGEOM.DIST(1,4,8,20,TRUE), independently 0.5345717234. Subtracting the cumulative result at two instead would ask for more than two and exclude the exact-two mass.

Feasible sample_s values range from MAX(0,number_sample−(number_pop−population_s)) through MIN(number_sample,population_s). For population ten, eight marked and sample size four, at least two must be marked because only two unmarked items exist.

In that second model, asking for an exact count of one is outside the documented support. Do not assume every mathematically impossible count is returned as numeric zero; Microsoft documents an error below the feasible lower bound.

If each of four draws were independent with fixed success probability 8/20=0.4, the exact-one binomial probability would be 4*0.4*0.6^3, independently 0.3456.

The without-replacement answer is about 0.3632611 because the category proportions change as items are removed. Choose the distribution from the sampling design, not simply whichever formula is familiar. A large-population approximation needs its own justification.

The reference documents integer truncation of arguments and #VALUE! for nonnumeric inputs; use integer counts and an explicit TRUE/FALSE flag. It documents #NUM! when sample_s is below its feasible lower bound or above MIN(number_sample,population_s).

It also lists #NUM! for nonpositive sample size, nonpositive population success count, either count above the population, or nonpositive population size. Some degenerate mathematical models allow zero values, but this guide follows the dedicated reference’s stated restrictions and makes no native exception claim.

Validate counts before calculation rather than relying on truncation to repair fractional data. Check that population totals reflect the actual population and that samples are uniformly chosen without replacement. The function cannot verify either condition from four numbers, and no native Excel edge-case execution occurred here.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft HYPGEOM.DIST reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.