The NEGBINOM.DIST function returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.
Syntax:= NEGBINOM.DIST(number_f,number_s,probability_s,cumulative)
The NEGBINOM.DIST function syntax has the following arguments:
- Number_f Required. The number of failures.
- Number_s Required. The threshold number of successes.
- Probability_s Required. The probability of a success.
-
Cumulative Required. A logical value that determines the form of the function. If cumulative is TRUE, NEGBINOM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
Example: Let’s look at some Excel NEGBINOM.DIST function examples and explore how to use the NEGBINOM.DIST function as a worksheet function in Microsoft Excel:
Syntax: =NEGBINOM.DIST(B1,B2,B3,TRUE)
Result: 0.118942261
Based on the Excel spreadsheet above, the following NEGBINOM.DIST examples would return:
Syntax: =NEGBINOM.DIST(C1,C2,C3,TRUE)
Result: 0.580590129
Syntax: =NEGBINOM.DIST(D1,D2,D3,TRUE)
Result: 0.778965831
Syntax: =NEGBINOM.DIST(E1,E2,E3,TRUE)
Result: 0.36328125
Syntax: =NEGBINOM.DIST(F1,F2,F3,TRUE)
Result: 0.806152344
Syntax: =NEGBINOM.DIST(B1,B2,B3,FALSE)
Result: 0.047210693
Syntax: =NEGBINOM.DIST(C1,C2,C3,FALSE)
Result: 0.080590129
Syntax: =NEGBINOM.DIST(D1,D2,D3,FALSE)
Result: 0.057564378
Syntax: =NEGBINOM.DIST(E1,E2,E3,FALSE)
Result: 0.13671875
Syntax: =NEGBINOM.DIST(F1,F2,F3,FALSE)
Result: 0.080566406
Note:
- The NEGBINOM.DIST function will truncate all numerical values to integers.
- #VALUE! error – Occurs when:
- The number_f, number_s, or probability_s arguments are not recognized as numeric values;
- The cumulative argument provided is not recognized as a numeric or a logical value.
- #NUM! error – Occurs when:
- The number_f provided is less than 0 or the supplied number_s is greater than 1.
- The probability_s provided is less than 0 or greater than 1.
- The NEGBINOM.DIST function was introduced in MS Excel 2010 and hence is unavailable in earlier versions. For older versions, we can use the NEGBINOMDIST function.
- The equation for the negative binomial distribution is:
where:x is number_f, r is number_s, and p is probability_s.
