Excel Statistical Functions: NEGBINOMDIST (828361)

MORE INFORMATION

Note Number_f is a non-negative integer, number_s is a positive integer, and p is a probability with 0 < p < 1.

The NEGBINOMDIST(number_f, number_s, p) function returns the probability of exactly number_f failures before the number of successes reaches number_s in independent Bernoulli trials, each of which has an associated probability p of success and probability 1-p of failure.

Syntax

NEGBINOMDIST(number_f, number_s, p)

Example Usage

In baseball, you can assume that a ".300 hitter" gets a hit (success) with a probability of 0.300 each time the hitter comes to bat (each trial). You can also assume that successive times at bat are independent Bernoulli trials. The table can be used to find the probability that such a hitter gets exactly 0, 1, 2, ..., or 20 non-hits (failures) before the third success. The entry in cell B26 also reveals that there is about a 1.6 percent chance that the hitter will have 21 or more failures before the third success.

To illustrate the NEGBINOMDIST function, follow these steps:

1. Create a blank Excel worksheet, and then copy the following table.
2. Click cell A1 in your blank Excel worksheet, and then on the Edit menu, click Paste so that the entries in the table fill cells A1:D26 in your worksheet.
3. After you paste the table into your new Excel worksheet, click Paste Options, and then click Match Destination Formatting.
4. While the pasted range is selected, on the Format menu, point to Column, and then click AutoFit Selection.

number of successes	3
success probability	0.3
number of failures, f	P(exactly f failures)	alternative computation	cumulative
0	=NEGBINOMDIST(A4,$B$1,$B$2)	=BINOMDIST($B$1- 1,A4+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A4 + $B$1, $B$2, TRUE)
1	=NEGBINOMDIST(A5,$B$1,$B$2)	=BINOMDIST($B$1- 1,A5+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A5 + $B$1, $B$2, TRUE)
2	=NEGBINOMDIST(A6,$B$1,$B$2)	=BINOMDIST($B$1- 1,A6+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A6 + $B$1, $B$2, TRUE)
3	=NEGBINOMDIST(A7,$B$1,$B$2)	=BINOMDIST($B$1- 1,A7+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A7 + $B$1, $B$2, TRUE)
4	=NEGBINOMDIST(A8,$B$1,$B$2)	=BINOMDIST($B$1- 1,A8+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A8 + $B$1, $B$2, TRUE)
5	=NEGBINOMDIST(A9,$B$1,$B$2)	=BINOMDIST($B$1- 1,A9+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A9 + $B$1, $B$2, TRUE)
6	=NEGBINOMDIST(A10,$B$1,$B$2)	=BINOMDIST($B$1- 1,A10+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A10 + $B$1, $B$2, TRUE)
7	=NEGBINOMDIST(A11,$B$1,$B$2)	=BINOMDIST($B$1- 1,A11+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A11 + $B$1, $B$2, TRUE)
8	=NEGBINOMDIST(A12,$B$1,$B$2)	=BINOMDIST($B$1- 1,A12+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A12 + $B$1, $B$2, TRUE)
9	=NEGBINOMDIST(A13,$B$1,$B$2)	=BINOMDIST($B$1- 1,A13+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A13 + $B$1, $B$2, TRUE)
10	=NEGBINOMDIST(A14,$B$1,$B$2)	=BINOMDIST($B$1- 1,A14+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A14 + $B$1, $B$2, TRUE)
11	=NEGBINOMDIST(A15,$B$1,$B$2)	=BINOMDIST($B$1- 1,A15+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A15 + $B$1, $B$2, TRUE)
12	=NEGBINOMDIST(A16,$B$1,$B$2)	=BINOMDIST($B$1- 1,A16+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A16 + $B$1, $B$2, TRUE)
13	=NEGBINOMDIST(A17,$B$1,$B$2)	=BINOMDIST($B$1- 1,A17+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A17 + $B$1, $B$2, TRUE)
14	=NEGBINOMDIST(A18,$B$1,$B$2)	=BINOMDIST($B$1- 1,A18+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A18 + $B$1, $B$2, TRUE)
15	=NEGBINOMDIST(A19,$B$1,$B$2)	=BINOMDIST($B$1- 1,A19+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A19 + $B$1, $B$2, TRUE)
16	=NEGBINOMDIST(A20,$B$1,$B$2)	=BINOMDIST($B$1- 1,A20+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A20 + $B$1, $B$2, TRUE)
17	=NEGBINOMDIST(A21,$B$1,$B$2)	=BINOMDIST($B$1- 1,A21+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A21 + $B$1, $B$2, TRUE)
18	=NEGBINOMDIST(A22,$B$1,$B$2)	=BINOMDIST($B$1- 1,A22+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A22 + $B$1, $B$2, TRUE)
19	=NEGBINOMDIST(A23,$B$1,$B$2)	=BINOMDIST($B$1- 1,A23+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A23 + $B$1, $B$2, TRUE)
20	=NEGBINOMDIST(A24,$B$1,$B$2)	=BINOMDIST($B$1- 1,A24+$B$1-1, $B$2, FALSE)*$B$2	=1 - BINOMDIST($B$1 - 1, A24 + $B$1, $B$2, TRUE)
21 or more	=1 - SUM(B4:B24)	=1 - SUM(C4:C24)

Cells B4:B24 show the probabilities of exactly f failures before the number of successes reaches the value in cell B1 (initially set to 3). With this initial value in cell B1, the most likely number of failures before the third success is 3, but any number of failures between 1 and 10 has at least a 5 percent chance of occurring. Chances of specific numbers of failures that are greater than 10 decline rather rapidly, but there is still about a 1.6 percent chance that the batter will endure 21 or more failures before the third success.

The NEGBINOMDIST Help file provides a formula for computing NEGBINOMDIST (where COMBIN(n, k) returns the number of combinations of size k in a population of size n):

Another approach is to recognize that the negative binomial and binomial distributions are closely related. For exactly k failures to occur before the r-th success, the following conditions must be true:

1. Exactly k failures and r-1 successes in the first k+r-1 trials must occur.
2. A success on the k+r-th trial must occur.

You can use the following formula to compute NEGBINOMDIST (where the two terms on the right side correspond to 1 and 2, respectively): These terms can safely be multiplied together because the outcome on the k+r-th trial is independent of the outcomes on the previous k+r-1 trials. On the worksheet, column C shows this computation. Notice that values in columns B and C are the same.

There is no cumulative version of NEGBINOMDIST, like there is for the BINOMDIST function. However, you can define a function to return the probability of number_f or fewer failures before the number_s-th success. This value is the same as the probability of number_s or more successes in the first number_f + number_s trials. This value is the complement of the probability of number_s-1 or fewer successes in the first number_f + number_s trials. You can use the following formula to compute the probability of number_f or fewer failures before the number_s-th success:The results of this formula are shown in column D of the worksheet.

Results in Earlier Versions of Excel

Knusel (see the "References" section in this article) documented instances where BINOMDIST does not return a numeric answer and yields #NUM! instead because of a numeric overflow. When numeric answers are returned by BINOMDIST, they are correct. The NEGBINOMDIST function exhibits similar behavior. Therefore, NEGBINOMDIST does not suffer from round off problems. However, in some extreme cases, NEGBINOMDIST returns #NUM! when it can compute an answer. Additionally, other functions exhibit this same type of behavior in earlier versions of Excel, including the following functions:

• CRITBINOM
• HYPGEOMDIST
• POISSON

#NUM! is returned only when the first argument of the COMBIN function, number_f + number_s - 1, is greater than or equal to 1,030. There are no computational problems as long as this argument is less than 1,030. In practice, such high values of number_f and number_s are not likely.

Results in Excel 2003

Microsoft has implemented a conditional algorithm in Excel 2003 because of the overflow issue and because NEGBINOMDIST is well-behaved when the overflow does not occur. The conditional algorithm uses NEGBINOMDIST code from earlier versions of Excel (the computational formula involving COMBIN) when number_f + number_s - 1 < 1030. When number_f + number_s - 1 >= 1030, Microsoft implemented an alternative plan to use the formula that calls BINOMDIST. For additional information about how this plan avoids calling COMBIN, click the following article number to view the article in the Microsoft Knowledge Base:

Conclusions

Inaccuracies in earlier versions of Excel occur only when the NEGBINOMDIST formula results in a call to COMBIN with a first argument that is greater than or equal to 1030. In these cases, NEGBINOMDIST returns #NUM! in earlier versions of Excel. This behavior occurs because COMBIN overflows. This behavior has been corrected in Excel 2003 by using an alternative procedure that calls BINOMDIST. BINOMDIST, in turn, also suffers from overflow problems in earlier versions of Excel. The article on BINOMDIST provides pseudocode that describes the BINOMDIST function's alternative plan that is invoked whenever the number of trials is greater than or equal to 1,030.

The following functions also exhibit similar behavior in earlier versions of Excel:

• BINOMDIST
• CRITBINOM
• HYPGEOMDIST
• POISSON

These functions also either return correct numeric results or #NUM! or #DIV/0!. An alternative algorithm that is similar to the one for BINOMDIST is implemented in Excel 2003 to return correct answers in cases where earlier versions return #NUM!.

For more information about NEGBINOMDIST, click Microsoft Excel Help on the Help menu, type negbinomdist in the Search for box in the Assistance pane, and then click Start searching to view the topic.