BINOM.DIST in Excel

Calculations with the binomial distribution formula can quite tedious and difficult. The reason for this is due to the number and types of terms in the formula.  As with many calculations in probability, Excel can be utilized to expedite the process. Background on the Binomial Distribution The binomial distribution is a discrete probability distribution. In order to use this distribution, we need to make sure that the following conditions are met: There are a total of n independent trials.  Each of these trials can be classified as a success or failure.The probability of success is a constant p. The probability that exactly k of our n trials are successes is given by the formula: C( n, k) pk (1 - p)n – k. In the above formula, the expression C( n, k) denotes the binomial coefficient. This is the number of ways to form a combination of k elements from a total of n. This coefficient involves the use of the factorial, and so C(n, k) n!/[k!(n – k)! ]. COMBIN Function The first function in Excel related to the binomial distribution is COMBIN. This function calculates the binomial coefficient C( n, k), also known as the number of combinations of k elements from a set of n. The two arguments for the function are the number n of trials and k the number of successes. Excel defines the function in terms of the following: COMBIN(number, number chosen) Thus if there are 10 trials and 3 successes, there are a total of C(10, 3) 10!/(7!3!) 120 ways for this to occur. Entering COMBIN(10,3) into a cell in a spreadsheet will return the value 120. BINOM.DIST Function The other function that is important to know about in Excel is BINOM.DIST. There are a total of four arguments for this function in the following order: Number_s is the number of successes. This is what we have been describing as k.Trials are the total number of trials or n.Probability_s is the probability of a success, which we have been denoting as p.Cumulative uses an input either of true or false to calculate a cumulative distribution. If this argument is false or 0, then the function returns the probability that we have exactly k successes. If the argument is true or 1, then the function returns the probability that we have k successes or less. For example, the probability that exactly three coins out of 10 coin flips are heads is given by BINOM.DIST(3, 10, .5, 0). The value returned here is 0.11788. The probability that from flipping 10 coins at most three are heads is given by BINOM.DIST(3, 10, .5, 1). Entering this into a cell will return the value 0.171875. This is where we can see the ease of using the BINOM.DIST function. If we did not use software, we would add together the probabilities that we have no heads, exactly one head, exactly two heads or exactly three heads. This would mean that we would need to calculate four different binomial probabilities and add these together. BINOMDIST Older versions of Excel use a slightly different function for calculations with the binomial distribution. Excel 2007 and earlier use the BINOMDIST function. Newer versions of Excel are backward compatible with this function and so BINOMDIST is an alternate way to calculate with these older versions.