Using Excel to compute the binomial distribution.

The exact binomial distributionWhat is the chance of exactly 16 heads out of 20 tosses?

If we assume that the coin toss is fair and the results are recorded properly, the results will follow what is called a binomial distribution. The equation that describes the binomial distribution is built-in to Excel:

   =BINOMDIST(16,20,0.5, FALSE)

That formula answers this question: What is the chance of getting exactly 16 ‘successes’ out of 20 tries, when the probability of success is 0.5 (since 50% of fair coin tosses are heads)? The answer is 0.46%, or about one time in 200. (Why ‘FALSE’? Read on for an explanation.)

The cumulative binomial distribution

The chance of observing 16 heads out of twenty coin flips is about 1 in 200. That’s pretty rare. But it is the answer to the wrong question. If we had observed 17 heads, we would have been even more surprised. So we need to add to the probability we calculated the chance of observing 17, 18, 19 or 20 heads. One way to do this is to use a formula similar to the one shown above (but changing ’16’ to the other values) and adding up the probabilities. Another way is to use this Excel formula:

   =1 - BINOMDIST(15,20,0.5,TRUE)

With the last argument to the function set to True, that function returns the probability of getting 15  or fewer successes out of 20 tries when the probability of success is 0.5. Subtract that result from 1 to get the probability of getting 16 or more successes. So to switch from calculating an exact probability to a cumulative one, we had to change the last argument to Excel’s function from False to True, and also had to change the first value from 16 to 15.

The answer is 0.59%.

Newer functions in Excel 2010 and later

Microsoft has created a new set of statistical functions intrdouced in Excel 2010. All of these use two (or more) word function names separated by period. The example at the top of this page is written as:

=BINOM.DIST(16,20,0.5, FALSE)

In some cases, the newer functions are more accurate than the older ones, so it makes sense to always use them.