HYPGEOM.DIST

What it does

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

HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) models a fixed-size sample drawn without replacement from a finite population. Every subset of the sample size has equal probability. FALSE returns the mass at exactly sample_s successes; TRUE returns the probability of at most that many.

Write the four counts as k,n,K,N: observed successes, sample size, population successes and population size. The success probability changes after each draw because the selected object is not replaced. A binomial model with one fixed probability answers a different sampling question.

Try it

=HYPGEOM.DIST(1,2,3,5,FALSE) is about 0.6. Drawing two objects from five, three marked successful, yields one success in choose(3,1)*choose(2,1)=6 of choose(5,2)=10 possible samples.

Quirks

The feasible mathematical success interval is max(0,n-(N-K))..min(n,K). Microsoft’s opened remarks specify #NUM! outside that interval and for nonpositive sample size, population success count or population size. Nonnumeric count arguments give #VALUE!.

Recorded build 20430 controls depart from those stated guards. HYPGEOM.DIST(0,0,0,5,0) returned one. With successes above the support, HYPGEOM.DIST(5,4,2,5,0) returned zero, while changing the cumulative flag from zero to one returned one. FN-HYPGEOM.DIST-001 retains all four calls, exact inputs and provenance.

The current code checks original negative signs before truncating the counts, so a negative fraction must not become an admitted zero merely through truncation. Its support shortcuts and mass body need separate tests; neither the probability definition nor the public error list settles every recorded numerical edge.

How to implement

For a mathematical implementation, count the supported selections:

mass(k) = choose(K,k) * choose(N-K,n-k) / choose(N,n)

The sample k=1,n=2,K=3,N=5 has six favourable subsets out of ten, giving probability 0.6. A recurrence between adjacent supported masses is a way to avoid constructing huge factorials, but it is an alternative algorithm.

The current compatibility candidate has a more specific composition:

1. Reject original negative counts, then truncate all four toward zero. Reject n>N or K>N.
2. For a mass, return zero for an unsupported k. Handle the degenerate n=0 and n=N cases explicitly.
3. Otherwise form the binary64 values p=n/N and q=(N-n)/N separately. Do not assume the stored q is interchangeable with 1-p.
4. Obtain shared binomial masses a=b(k;K,p,q), b=b(n-k;N-K,p,q) and c=b(n;N,p,q). Return (a*b)/c using the current binary64 multiplication and division order.
5. For cumulative calls, the current code returns one immediately when k>=min(n,K); otherwise it adds masses from zero through k and caps the sum at one.

The shared binomial body, small-tail choices and publication behaviour retain unresolved differences in the problem report. The generated fingerprint report says no complete hypergeometric algorithm is adopted. Treat this as a codeable current candidate, with the named recorded endpoint controls, rather than a whole-domain Excel identification.

Rust code

77 hypgeom_dist "HYPGEOM.DIST" (5, 5) [V "sample_s", V "number_sample", V "population_s", V "number_pop", V "cumulative"] |s, a, sv| {
    let [ss, ns, ps, np] = nums::<4>(s, a)?;
    out(s, dd::hypergeom_dist_kernel(ss, ns, ps, np, flag(s, a, 4)?))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Concrete controls from the 29 September 2026 build 20430 capture:

Inputs were written as exact doubles through bulk Value2 cells. The capture records Windows 64-bit and Compatibility Version 2; it does not record CPU. These controls do not compare builds.

Recorded environments and comparison details.

Alternatives

HYPGEOMDIST is the mass-only compatibility spelling. BINOM.DIST describes independent trials with one fixed probability, and COMBIN counts selections.

Named variants

No named variants are recorded yet.