HYPGEOMDIST in Zoho Sheet: Exact Sampling Without Replacement

HYPGEOMDIST predicts the probability of observing exactly x successes among n items drawn without replacement from a finite population of N items, of which exactly M are successes. Zoho’s documented signature is HYPGEOMDIST(x; n; M; N). For example, if a box has 30 parts, nine defective, and you sample five without replacing each part before the next draw, the modelled chance of exactly two defective parts is =HYPGEOMDIST(2;5;9;30) ≈ 0.335985853227 (33.60%). That is an independently calculated hypergeometric probability; it was not obtained by running Zoho. Z1

This function is useful for inspections, card draws, sample audits and drawing items from a fixed, known population. It is not interchangeable with BINOMDIST when each draw changes the population composition. If you are replacing items after each draw, independence and a constant success rate could justify a binomial model instead, subject to how the real process works.

HYPGEOMDIST(x; n; M; N)

All four inputs are required by the English Zoho Help signature; the documentation does not describe default parameters or a cumulative-mode argument. M and N refer to the original population, not the sample. The x support also has a lower bound max(0,n-(N-M)): drawing too many items forces some successes if too few failures exist. A mathematically impossible outcome has probability zero in the distribution, but Zoho’s Help error list does not establish whether every such impossible support value returns zero or an error. Do not invent an implementation rule. Z1

The combinatorial mass function is C(M,x)·C(N−M,n−x)/C(N,n), where C(a,b) is the number of ways to choose b objects from a. This is a statistical identity used for independent result checks. Sampling must be random so that all subsets of n items are equally likely; a biased inspection method invalidates the probability interpretation.

Create headers A1–F1: Defects in sample, Sample drawn, Defective in box, Box size, Exact probability, Cumulative probability. Enter x values 0 to 5 in A2:A7, the same n=5, M=9 and N=30 in B2:D7, and put =HYPGEOMDIST(A2;B2;C2;D2) in E2. Fill E2 down through E7. In F2 use =SUM($E$2:E2) and fill down to F7; column F is an independently constructed cumulative total, not a documented optional HYPGEOMDIST mode.

The six exact probabilities sum to 1 under the hypergeometric model. In this example the chance of two or more defects is =SUM(E4:E7) ≈ 0.479221927498 (47.92%). The equivalent complement is =1-SUM(E2:E3). Those proposed spreadsheet expressions are mathematically equivalent; native Zoho parsing/output remains untested.

A common reporting mistake is to read the value for x=2 as at least two defects. It describes exactly two. To test “at least” or “at most,” explicitly sum the relevant exact values, as in the cumulative column. Also do not label a failure count as a success count just because it is more convenient: swapping defective/non-defective categories changes the interpretation of M and x.

A second small example you can hand-check

Consider 10 unique trading cards, four holographic, and a random four-card hand with no replacement. The chance that exactly one card is holographic is =HYPGEOMDIST(1;4;4;10). Independently, that equals C(4,1)×C(6,3)/C(10,4) = 4×20/210 = 0.380952380952. This tiny example can be validated on paper without trusting a spreadsheet function, and makes the role of each parameter visible. If a card is put back and the deck reshuffled after each draw, the relevant model changes.

Risk threshold: For the inspection example, the probability of no defective sample parts is =HYPGEOMDIST(0;5;9;30) ≈ 0.142793987622. That is the probability of a clean five-part sample even when the known box contains nine defects. It is not the probability the box itself is defect-free. Be careful about interpreting sampling results as posterior inferences without a prior model.

Expected count: The modelled expected number of successes is n·M/N = 5×9/30 = 1.5, not necessarily an integer observed count. The variance is n·(M/N)·(1−M/N)·(N−n)/(N−1) for N>1 = 0.905172413793 in this example. The finite-population correction (N−n)/(N−1) distinguishes without-replacement dispersion from an otherwise similar binomial model. These are independently checked distribution facts, not undocumented Zoho functions.

Quality acceptance rule: If management decides a lot is acceptable only when the sample contains zero or one defect, the modelled acceptance probability, given nine defects in a 30-part box, is =SUM(E2:E3) ≈ 0.520778072502. Choosing a different sample size n changes the probability; compare choices before defining the acceptance threshold. The calculation assumes the population’s actual M and N are already known, which is rarely guaranteed in real quality control.

Scaling: For huge N or extreme parameter combinations, combinatorial numbers may overflow an unsuited manual arithmetic implementation. Using the documented function is conceptually preferable to computing large factorials in cells. Zoho’s article does not document its internal precision algorithm or numerical limits; do not claim native accuracy at extreme magnitudes without experiments.

• #NUM!: Zoho explicitly lists x<0, x>n, x>M, n<0, n>N, M<0, M>N, or N<0 as invalid conditions. It omits some other mathematically invalid situations, so do not treat this list as an exhaustive implementation contract. Z1
• Noninteger values: Count arguments represent whole items. Zoho does not explicitly say whether noninteger numeric inputs are rejected or truncated; Excel’s documented truncation is not evidence for Zoho. Enter integers deliberately.
• Wrong order: HYPGEOMDIST(2;5;30;9) swaps M and N and is nonsensical because population successes exceed population size. Map fields before building cell references.
• #VALUE!, #NAME!, #REF!: Zoho’s generic help associates these with invalid types, misspelled names and invalid or removed references. Verify that input cells really hold numbers rather than text, and that copied formulas still refer to the intended four columns.
• Unexpected probability 0: Check whether x is feasible given both successes and failures in the original population, whether n is positive and whether labels are reversed. Decimal formatting can hide small nonzero results.
• Unexpected total not 1: Include the complete feasible x support; a short sample table or a rounding error can leave a visible discrepancy. Test with full-precision cell values, not rounded display values.

Zoho documents the four-argument exact-probability HYPGEOMDIST(x;n;M;N). Microsoft documents modern HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative), which adds a required fifth mode argument to choose cumulative versus exact. Hence a five-argument Excel formula should not be copied unchanged into the four-argument Zoho signature. Excel’s documented integer truncation is Excel-specific, with Zoho coercion still untested. Zoho HYPGEOMDIST · Microsoft HYPGEOM.DIST.

For with-replacement fixed-n sampling, compare the separately documented BINOMDIST model; for failure-counts until a target number of successes, see NEGBINOMDIST. For random counts over a fixed interval, see POISSON. These are checked official Zoho documentation links, not live internal Spreadsheet-Tutorials article URLs.

Zoho Sheet evidence: This guide covers the English-language Zoho Sheet function documented in the linked official Zoho Help article, inspected 13 October 2026 (UTC). Zoho describes Sheet as an online spreadsheet product and publishes function reference pages without always specifying a function’s first supported release, rollout cohort, plan, device, mobile app edition, numerical precision guarantees or offline calculation support. Accordingly, none of those compatibility details is asserted here. Check the target account and client before claiming parity across web, Windows, macOS, Android, iOS or iPadOS. The Help knowledge base currently lists those client sections, but their existence does not establish function-level equivalence. Zoho Function Catalogue · Zoho Product Help.

Formula delimiters: The English reference writes arguments separated by semicolons (;), as do the examples here. Zoho’s workbook settings let the author change locale, decimal separator and digit grouping, and changes are shared with collaborators. Confirm parsing with the workbook actually used; do not assume formulas copied from comma-delimited Excel examples parse unchanged. Zoho Locale Settings. Dates and workbook numeric displays can also vary by locale.

Validation classification: OFFICIAL_DOCUMENTATION_RESEARCH establishes the vendor’s published names, argument order, stated modes and documented errors; INDEPENDENT_CALCULATION establishes the mathematical expected results on this page; NATIVE_ZOHO_EXECUTION_NOT_PERFORMED means no Zoho workbook/account was used to execute or screenshot these examples. A mathematical identity is not evidence of undocumented coercion or of how a particular Zoho release rounds or displays a cell.

Is this a native Zoho Sheet formula? The function name and documented signature come from official Zoho Help. The example results are independently calculated predictions; they have not been run in a Zoho session.

Can I paste the Excel version of this formula? Compare both the function name and required argument count. The compatibility paragraph below gives an evidence-based Excel distinction, and the actual workbook’s syntax and separators must be checked.

Why does an answer display as 0 or 1? Probabilities may be close to a boundary, or the cell’s number format may be rounding away significant digits. Increase displayed decimal places, verify the data scale and use a mathematically suitable model; do not equate displayed rounding with proof of exact 0 or 1.

Where do these examples come from? The worksheet inputs are invented, openly disclosed practice datasets. The outputs come from independent SciPy calculations and mathematical formulas—not from the illustrative, potentially different, private input cells in Zoho’s official screenshots or embedded demo.

• Z1 Official Zoho Sheet documentation for this function, inspected 13 October 2026: purpose, syntax, argument descriptions, examples and vendor error entries.
• [Z2] Zoho Sheet statistical functions catalogue, inspected 13 October 2026: placement in its statistical reference; catalogue page is not a native execution log.
• [Z3] Zoho Sheet spreadsheet locale and calculation settings, inspected 13 October 2026: workbook locale and separators.
• M1 Microsoft Support documentation for the named Excel counterpart, inspected 13 October 2026: Excel-specific comparison only, not proof of Zoho behaviour.