You are currently offline, waiting for your internet to reconnect

`BINOMDIST(x, n, p, cumulative)`

`x`is a non-negative integer`n`is a positive integer- 0 <
`p`< 1 `Cumulative`is a logical variable that takes on the values TRUE or FALSE

- In baseball, a ".300 hitter" hits (succeeds) with probability 0.300 each time he comes to bat (each trial).
- Successive times at bat are independent Bernoulli trials.

If the batter gets 50 hits in his first 200 trials (a .250 average), he must get 100 hits in his next 300 trials to have 150 hits and a .300 average over 500 trials. You can use the following table to analyze the chance that the batter gets sufficient hits to maintain his average. Baseball commentators frequently allude to the "law of averages" when they say that fans do not have to worry about the performance of this batter with only 50 hits in his first 200 trials because "by the end of the season his average will be .300." If the trials really were independent, and the batter really had a 0.3 chance of success on any one trial, this reasoning is fallacious because the outcomes of the first 200 trials do not affect the success or the failure over the last 300 trials.

To illustrate the use of BINOMDIST, create a blank Excel worksheet, copy the following table, select cell

number of trials | 10 | |

success probability | 0.3 | |

successes, x | P(exactly x successes) | P(x or fewer successes) |

0 | =BINOMDIST(A4,$B$1,$B$2,FALSE) | =BINOMDIST(A4,$B$1,$B$2,TRUE) |

1 | =BINOMDIST(A5,$B$1,$B$2,FALSE) | =BINOMDIST(A5,$B$1,$B$2,TRUE) |

2 | =BINOMDIST(A6,$B$1,$B$2,FALSE) | =BINOMDIST(A6,$B$1,$B$2,TRUE) |

3 | =BINOMDIST(A7,$B$1,$B$2,FALSE) | =BINOMDIST(A7,$B$1,$B$2,TRUE) |

4 | =BINOMDIST(A8,$B$1,$B$2,FALSE) | =BINOMDIST(A8,$B$1,$B$2,TRUE) |

5 | =BINOMDIST(A9,$B$1,$B$2,FALSE) | =BINOMDIST(A9,$B$1,$B$2,TRUE) |

6 | =BINOMDIST(A10,$B$1,$B$2,FALSE) | =BINOMDIST(A10,$B$1,$B$2,TRUE) |

7 | =BINOMDIST(A11,$B$1,$B$2,FALSE) | =BINOMDIST(A11,$B$1,$B$2,TRUE) |

8 | =BINOMDIST(A12,$B$1,$B$2,FALSE) | =BINOMDIST(A12,$B$1,$B$2,TRUE) |

9 | =BINOMDIST(A13,$B$1,$B$2,FALSE) | =BINOMDIST(A13,$B$1,$B$2,TRUE) |

10 | =BINOMDIST(A14,$B$1,$B$2,FALSE) | =BINOMDIST(A14,$B$1,$B$2,TRUE) |

300 trials, success probability 0.3: | ||

successes, x | P(exactly x successes) | P(x or fewer successes) |

89 | =BINOMDIST(A18,300,0.3,FALSE) | =BINOMDIST(A18,300,0.3,TRUE) |

90 | =BINOMDIST(A19,300,0.3,FALSE) | =BINOMDIST(A19,300,0.3,TRUE) |

99 | =BINOMDIST(A20,300,0.3,FALSE) | =BINOMDIST(A20,300,0.3,TRUE) |

100 | =BINOMDIST(A21,300,0.3,FALSE) | =BINOMDIST(A21,300,0.3,TRUE) |

101 | =BINOMDIST(A22,300,0.3,FALSE) | =BINOMDIST(A22,300,0.3,TRUE) |

- In Microsoft Office Excel 2007, click the
**Home**tab, click**Format**in the**Cells**group, and then click**AutoFit Column Width**. - In Excel 2003 and in earlier versions of Excel, point to
**Column**on the**Format**menu, and then click**AutoFit Selection**.

Cells B4:B14 show the probabilities of exactly

B18:B20 show that the most likely number of successes in 300 trials is 90. The probability of exactly

Note 1 Knusel, L. "On the Accuracy of Statistical Distributions in Microsoft Excel 97", Computational Statistics and Data Analysis (1998), 26: 375-377.

For the non-cumulative case, BINOMDIST(

`COMBIN(n,x)*(p^x)*((1-p)^(n-x))`

COMBIN has not been modified for Excel 2003 and for later versions of Excel.

The algorithm uses BINOMDIST code from earlier versions of Excel (the computational formula mentioned earlier in this article) when

Typically, COMBIN overflows because it is astronomical, but

Microsoft's approach calculates an unscaled sum of all the probabilities of exactly

The algorithm takes advantage of the fact that the ratio of successive terms of the form COMBIN(

Step 0: (Initialization). Initialize the TotalUnscaledProbability and the UnscaledResult properties to 0. Initialize the constant EssentiallyZero to a very small number, for example, 10^(-12).

Step 1: Find

`TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;`

`PreviousValue = 1;Done = FALSE;k = m + 1;While (not Done && k <= n) { CurrentValue = PreviousValue * (n – k + 1) * p / (k * (1 – p)); TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue; If (k == x) then UnscaledResult = UnscaledResult + CurrentValue; If (cumulative && k < x) then UnscaledResult = UnscaledResult + CurrentValue; If (CurrentValue <= EssentiallyZero) then Done = TRUE; PreviousValue = CurrentValue; k = k+1; }end While;`

`PreviousValue = 1;Done = FALSE;k = m - 1;While (not Done && k >= 0) { CurrentValue = PreviousValue * k+1 * (1-p) / ((n – k) * p); TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue; If (k == x) then UnscaledResult = UnscaledResult + CurrentValue; If (cumulative && k < x) then UnscaledResult = UnscaledResult + CurrentValue; If (CurrentValue <= EssentiallyZero) then Done = TRUE; PreviousValue = CurrentValue; k = k-1; }end While;`

`Return UnscaledResult/TotalUnscaledProbability;`

To illustrate this, copy the following table, select cell

=D5*(1-$B$2)*(A4+1)/($B$2*($B$1-A4)) | =D4/$D$15 |

=D6*(1-$B$2)*(A5+1)/($B$2*($B$1-A5)) | =D5/$D$15 |

1 | =D6/$D$15 |

=D6*$B$2*($B$1-A7+1)/((1-$B$2)*A7) | =D7/$D$15 |

=D7*$B$2*($B$1-A8+1)/((1-$B$2)*A8) | =D8/$D$15 |

=D8*$B$2*($B$1-A9+1)/((1-$B$2)*A9) | =D9/$D$15 |

=D9*$B$2*($B$1-A10+1)/((1-$B$2)*A10) | =D10/$D$15 |

=D10*$B$2*($B$1-A11+1)/((1-$B$2)*A11) | =D11/$D$15 |

=D11*$B$2*($B$1-A12+1)/((1-$B$2)*A12) | =D12/$D$15 |

=D12*$B$2*($B$1-A13+1)/((1-$B$2)*A13) | =D13/$D$15 |

=D13*$B$2*($B$1-A14+1)/((1-$B$2)*A14) | =D14/$D$15 |

=SUM(D4:D14) |

To calculate the probability of 3 or fewer successes, type the following formula in any blank cell:

= SUM(D4:D7)/D15

In the previous example, EssentiallyZero does not stop Steps 2 or 3. However, if you want to evaluate BINOMDIST(550, 2000, 0.3, TRUE), EssentiallyZero may stop Step 2 or Step 3. A binomial random variable with The CRITBINOM, HYPGEOMDIST, NEGBINOMDIST, and POISSON function exhibit similar behavior in earlier versions of Excel. These functions also return either correct numeric results or #NUM! or #DIV/0!. Again, problems occur because of overflow (or underflow).

It is easy to determine when and how these problems occur. Excel 2003 and later versions of Excel use an alternative algorithm that is similar to the one for BINOMDIST to return correct answers in cases where earlier versions of Excel return #NUM!.

Properties

Article ID: 827459 - Last Review: 09/19/2011 00:04:00 - Revision: 5.0

- Microsoft Office Excel 2007
- Microsoft Excel 2004 for Mac

- kbexpertisebeginner kbinfo KB827459