Need to hypergeometric distribution calculations? Microsoft Excel supplies a statistical functions for just such a purpose says bestselling computer book author Stephen L. Nelson
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.
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
