Excel provides a sometimes useful statistical function for making hyper geometric distribution calculations. (Note: People sometimes call the distribution “hypergeometric” [one word] rather than a “hyper geometric” [two words].)
Background information on Hyper Geometric Distributions
The hyper geometric probability distribution is much like the binomial probability distribution. The hyper geometric distribution describes the outcome of a multi-step experiment, consisting of n trials, where each trial ends in either a success or a failure.
But unlike the binomial distribution, the trials are not independent-so success in one trial affects the probability of success in another trial and the probability of success changes from trial to trial.
Arguments for the Hyper Geometric Distribution Function
The HYPGEOMDIST is therefore used when samples are taken from a finite population but not replaced for the next trial. The HYPGEOMDIST function uses the following syntax:
Example of a Hyper Geometric Distribution Function
For example, suppose a shipment of 10 items has 2 defective items and 8 non-defective items.
If you randomly select and test the individual units and set aside the units you’ve tested, the
probability of finding a defective unit changes depending on what’s left in the shipment.
Suppose that you must reject a shipment if you find a single defective unit. If you sample 3
items, what’s the probability that the shipment will be accepted? To find out, you can call finding
a defective item a “success,” and enter the HYPGEOMDIST function to look like this:
Understanding Hyper Geometric Distribution Function Results
This means 0 “successes” in 3 trials when there are 2 “successes” in the population of 10. The function returns the value 0.4667.
The probability of rejecting the shipment is 1-0.4667, or 0.5333. To verify this, you can add
the probability of getting 1 success with the probability of having 2 successes.
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.