Hypergeometric Distributions with Microsoft Excel

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 accountant and bestselling computer book author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself kits that businesses and investors can use for setting up a Montana S Corporation or a Nebraska S Corporation

Article Source: http://EzineArticles.com/?expert=Stephen_Nelson

By Stephen Nelson