Binomial distribution
First we will learn how to evaluate binomial distribution with the help of Excel. Let’s flip a coin n times and count how many tails we are going to get.
If n=0, then no coin is tossed and the only option is 0 tails. Therefore P(0)=1, the mean is 0 and the standard deviation is 0. We are going to do a simple table for n=0. First we will define columns. Click on cell A1 and type X for our variable. Click on enter. Then click on B1 and input p(X) for probability. Click on cell A2 and type 0. Click on enter. Click on cell B2 and type 1. That completes the table. Now let’s also evaluate mean and standard deviation. Click on cell D1 and type mean. Click on cell E1 and type variance. Click on cell F1 and type SD. Let’s put in the formulas. Click on cell D2 and type =a2*b2. Click on cell E2 and type =b2*(a2-d2)^2. Click on cell F2 and type =sqrt(e2). We will indicate that we have a binomial distribution by clicking on H1 and typing in n, then we’ll go to H2 and type 0. We’ll go to I1 and type p and then we’ll go to I2 and type =1/2.
Now we will do n=1. First we will define columns. Click on cell A4 and type X for our variable. Then click on B4 and input p(X) for probability. Click on cell D4 and type mean. Click on cell E4 and type variance. Click on cell F4 and type SD. When n=1 the only two options are 0 tails and 1 tail. Thus, we click on cell A5 and type 0 and we click on cell A6 and type 1. Let’s now use binomial formula to evaluate probabilities. Click on cell B5 and type =BINOMDIST(0,1,1/2,FALSE). Click on enter. In this formula for binomial distribution there are four parameters, the first means we are looking for the probability of getting 1 tail. The second means we are flipping 1 coin. The third means that the probability of getting a tail in a flip is ½. The last parameter False says that we are not looking into cumulative probability. We may use True instead when we are looking for the median for example, but that is not the topic here. Now that we learned that, we will evaluate probability of getting 1 coin. Click on cell B6 and type =BINOMDIST(1,1,1/2,FALSE). We will indicate that we have a binomial distribution by clicking on H4 and typing in n, then we’ll go to H5 and type 1. We’ll go to I4 and type p and then we’ll go to I5 and type =1/2. Now let’s evaluate mean and standard deviation. Click on cell D4 and type mean. Click on cell E4 and type variance. Click on cell F4 and type SD. Click on cell D5 and type =h5*i5. Click on cell E5 and type =h5*i5*(1-i5). Click on cell F6 and type =sqrt(e5).
Let’s now do n=2. We will indicate that we have a binomial distribution by clicking on H8 and typing in n, then we’ll go to H9 and type 2. We’ll go to I8 and type p and then we’ll go to I9 and type =1/2.Click on cell A8 and type X for our variable. Then click on B8 and input p(X) for probability. Click on cell D8 and type mean. Click on cell E8 and type variance. Click on cell F8 and type SD. When n=2 the only three options are 0, 1 and 2 tails. Thus, we click on cell A9 and type 0, we click on cell A10 and type 1 and we click on cell A11 and type 2. Let’s now use binomial formula to evaluate probabilities. Click on cell B9 and type =BINOMDIST(0,2,1/2,FALSE). Click on enter. The second parameter here means we are flipping 2 coins. Click on cell B10 and type =BINOMDIST(1,2,1/2,FALSE). Click on cell B11 and type =BINOMDIST(2,2,1/2,FALSE). Now let’s evaluate mean and standard deviation. . Click on cell D8 and type mean. Click on cell E8 and type variance. Click on cell F8 and type SD. Click on cell D9 and type =h9*I9 Click on cell E9 and type =h9*I9*(1-I9). Click on cell F9 and type =sqrt(e9).
We now study what happens when flipping 3 coins. Click on cell A13 and type X for our variable. Then click on B13 and input p(X) for probability. Click on cell D13 and type mean. . Click on cell E13 and type variance. Click on cell F13 and type SD. . We will indicate that we have a binomial distribution by clicking on H13 and typing in n, then we’ll go to H14 and type 3. We’ll go to I13 and type p and then we’ll go to I14 and type =1/2. When flipping 3 coins, you can get 0, 1, 2 or 3 tails. Thus, we click on cell A14 and type 0, we click on cell A15 and type 1, we click on cell A16 and type 2 and we click on cell A17 and type 3. Let’s now use binomial formula to evaluate probabilities. Click on cell B14 and type =BINOMDIST(0,3,1/2,FALSE). Click on enter. The second parameter here means we are flipping 3 coins. Click on cell B15 and type =BINOMDIST(1,3,1/2,FALSE). Click on cell B16 and type =BINOMDIST(2,3,1/2,FALSE). Click on cell B17 and type =BINOMDIST(3,3,1/2,FALSE). Now let’s evaluate mean and standard deviation. . Click on cell D13 and type mean. Click on cell E13 and type variance. Click on cell F13 and type SD. Click on cell D14 and type =h14*i14. Click on cell E14 and type =h14*i14*(1-i14). Click on cell F14 and type =sqrt(e14).
Exercises
Exercise 1. Make the binomial probability distribution table for n=4 and p=1/2.
Exercise 2. Make the binomial probability distribution table for n=5 and p=1/2.
Exercise 3. Make the binomial probability distribution table for n=6 and p=1/3.
Exercise 4. Make the binomial probability distribution table for n=8 and p=1/3.
