Binomial Probability Distributions in Excel

Save

The binomial distribution is a way to calculate the probability of a certain number of "successes" (desired outcomes) out of a certain number of independent yes/no trials. Whereas the binomial distribution is often taught in terms of a 50/50 coin flip, the probabilities of the two outcomes can be anything, as long as they add up to 1. The spreadsheet program Excel has an easy, built-in way to compute probabilities in the binomial distribution.

Calculator

  • Calculating binomial distribution probabilities with a calculator is tedious. For example, for an event with a 1 percent (0.01) chance of happening, if you want to know the chances of the event occurring fewer than two times in 50 trials, you would use the formula P(X < 2) = P(X = 0) + P(X = 1) = 50! / (0!50!) .01^0.99^50 + 50! / (1!49!) .01^1.99^49 = .914. Fortunately, the same question is easily handled in Excel.

Excel

  • In Excel, to do the same problem, you would select Function from the Insert menu, then find the statistical function called BINOMDIST. The BINOMDIST function takes four arguments: Number_s -- the number of "successes" in the trial, Trials -- the number of trials in your experiment, Probability_s -- the probability of a success in a trial, and Cumulative -- is the probability to be calculated cumulative (true) or not (false). You can also type these directly into the cell, such as = BINOMDIST(1,50,0.01,TRUE).

Not Cumulative

  • If the cumulative value is set to FALSE, the function returns the probability of exactly the given number of successes. This is not for problems with wording like "at least three times" or "more than four times."

Cumulative

  • Setting the value for cumulative to TRUE gives the probability of the given number of successes or fewer; for example, the probability of getting 0, 1, 2, 3, or 4 successes. To get the probability of a certain number of successes or more, there is a simple trick. Find the probability of getting the opposite result (i.e., anything less than the desired number of successes) and subtract this from 1. So, for a problem like "Find the probability of flipping 16 or more heads in 20 coin flips," instead take 1 - BINOMDIST(15,20,0.5,TRUE) to subtract the probability of getting 15 heads or fewer from 1, the total probability of all possible outcomes.

References

  • Photo Credit Jupiterimages/Photos.com/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!