Swapping population and draw size
HYPGEOM.DIST has separate n, M and N arguments. A draw count cannot exceed its population.
Compare finite sampling with a replacement model
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in E2:E6. A finite population has B2 total records and B3 marked records; choose a uniformly random subset of B4 records without replacement. E2 is the probability of exactly B5 marked records, E3 is at most B5 and E4 is at least B6. E5 is the expected marked count. E6 is the contrasting exact-count probability if the B4 draws were instead independent with replacement, each having event proportion B3/B2. Do not use that contrasting model for E2:E4.
Edit the event model
Apply one numeric value to the actual worksheet. Counts, proportions and interval boundaries keep their separate meanings. The complete model is validated before an edit replaces a valid input.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E2, E3, E4, E5, E6. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Calculate a sample-count report without replacement, then compare it with independent draws at the same starting proportion.
THE QUICK ANSWER01
Calculate a sample-count report without replacement, then compare it with independent draws at the same starting proportion.
E2
=IF(OR($B$4=0,$B$3=0),1,HYPGEOM.DIST($B$5,$B$4,$B$3,$B$2,FALSE))E3
=IF(OR($B$4=0,$B$3=0),1,HYPGEOM.DIST($B$5,$B$4,$B$3,$B$2,TRUE))E4
=IF($B$6<=MAX(0,$B$4+$B$3-$B$2),1,IF($B$6>MIN($B$4,$B$3),0,SUM(HYPGEOM.DIST(SEQUENCE(MIN($B$4,$B$3)-$B$6+1,1,$B$6),$B$4,$B$3,$B$2,FALSE))))E5
=$B$4*$B$3/$B$2E6
=IF($B$4=0,1,BINOM.DIST($B$5,$B$4,$B$3/$B$2,FALSE))For the example data
E2 0.3521671826625387E3 0.8686790505675954E4 0.48348813209494323E5 1.5E6 0.3087000000000001
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Read the event before choosing a function
Enter separate formulas in E2:E6. A finite population has B2 total records and B3 marked records; choose a uniformly random subset of B4 records without replacement. E2 is the probability of exactly B5 marked records, E3 is at most B5 and E4 is at least B6. E5 is the expected marked count. E6 is the contrasting exact-count probability if the B4 draws were instead independent with replacement, each having event proportion B3/B2. Do not use that contrasting model for E2:E4.
Keep the supplied model explicit
Use a whole population N from 1–120, whole M and n from 0–N, exact k within MAX(0,n+M-N)–MIN(n,M), and a whole threshold t from 0 through n+1. Every subset of size n is assumed equally likely; this course does not infer a real sampling design. Requested positive outputs must remain representable in the supported numeric precision.
Retain full computed precision
Probability outputs are proportions, not percentages entered as whole numbers. Grading compares nonzero values relatively, so a rare positive event cannot pass as numeric zero. Full-precision copies keep the actual formula and result. The fitted or assumed model is not a guarantee of real outcomes.
Read the full step-by-step walkthrough
- Keep the population and sample distinct
N counts all available records; M counts the marked records among them. n is how many are drawn. Without replacement, each draw changes the remaining composition.
- Check which counts are possible
The marked count ranges from MAX(0,n+M-N) through MIN(n,M). The exact-count input must lie in this feasible support. A full census forces X=M; an empty sample forces X=0.
- Select exact or cumulative results
HYPGEOM.DIST takes k, sample size n, marked population M and total population N in that order. FALSE selects exactly k; TRUE includes feasible counts through k. Excel rejects zero draw sizes and zero marked populations, so the valid degenerate mathematical cases are handled explicitly with IF before calling HYPGEOM.DIST.
- Sum only a feasible inclusive upper tail
A threshold at or below the support minimum is certain. A threshold above its maximum is impossible. Otherwise sum the individual masses from t through MIN(n,M).
- Compare replacement as a separate assumption
Both models have expected count n*M/N, but their exact-count probabilities generally differ. The binomial comparison keeps a constant event probability; it does not describe the finite draw.
Things to watch
Treating draws as independent
Without replacement, later draws depend on earlier ones. The binomial output is a contrast, not a replacement for the requested result.
Ignoring a forced support boundary
When the sample is large, some marked records are unavoidable. The lower support can be greater than zero.
About this example
Original source models checked with real browser formulas and independent combinatorial/series targets. Zero-size/zero-marked mathematical cases use explicit guards before HYPGEOM.DIST, whose native contract rejects those arguments. Native execution remains separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.