How to use NEGBINOM.DIST Function in Excel

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:

NEGBINOM.DIST Function - How to use NEGBINOM.DIST Function in Excel

Syntax:  =NEGBINOM.DIST(B1,B2,B3,TRUE)

Result: 0.118942261

NEGBINOM.DIST Function in Excel 2 - How to use NEGBINOM.DIST Function in Excel

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:

  1. The NEGBINOM.DIST function will truncate all numerical values to integers.
  2. #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.
  3. #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.
  4. 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.
  5. The equation for the negative binomial distribution is:
    NEGBINOM.DIST Function in Excel - How to use NEGBINOM.DIST Function in Excel
    where:

    x is number_f, r is number_s, and p is probability_s.

READ:  How to use T.DIST.2T Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *