#### Latest posts by techwriter (see all)

- Test Your Knowledge of 4 Basic Fonts – Drag & Drop - January 27, 2017
- How NOT to Design a Web Site - January 25, 2017
- Hazards of Poorly Written Technical Documentation - December 26, 2016

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:

=HYPGEOMDIST(successes_in_sample,sample_size,number_of_successes,population)

**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:

=HYPGEOMDIST(0,3,2,10)

**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.*