NEGBINOM.DIST Function - Returns the negative binomial distribution in Excel

This article introduces the NEGBINOM.DIST function - one of the commonly used statistical functions in Excel.

Description: Returns the negative binomial distribution, the probability of having Number_f failures before the Number_s-th success, with the success probability being Probability_s. Supported in Excel 2010 and later versions.

Syntax: NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)

Where:

- number_f: Number of failures, a required parameter.

- number_s: Success threshold, a required parameter.

- probability_s: Success probability, a required parameter.

- cumulative: Logical value determining the form of the function, a required parameter including:

+ cumulative = True -> returns the cumulative distribution function.

+ cumulative = False -> returns the probability mass function.

Note:

- If number_f, number_s are not integers -> they are rounded to integers.

- Any argument of the function except cumulative is not a number -> the function returns the error value #VALUE!

- If probability_s < 0 or probability > 1 -> the function returns the error value #NUM!

- If number_f < 0 or number_s < 1 -> the function returns the error value #NUM!

The negative binomial distribution equation is:

nb(x;r,p)=(x+r−1r−1)p′(1−p)N$nb\left(x;r,p\right)=\left(\begin{array}{c}x+r-1\\ r-1\end{array}\right)p^{\prime }{\left(1-p\right)}^N$

Here:

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

For example:

Find the cumulative negative binomial distribution and probability with the values in the data table below:

- Calculate the cumulative negative binomial distribution (corresponding to the value True). In the cell where you want to calculate, enter the formula: =NEGBINOM.DIST(D6,D7,D8,D9)

- Press Enter -> the cumulative negative binomial distribution is:

- Calculate the negative binomial distribution probability (corresponding to the value False). In the cell where you want to calculate, enter the formula: =NEGBINOM.DIST(D6,D7,D8,D10)

- Press Enter -> the negative binomial distribution probability is:

- There is a small difference in value between the cumulative negative binomial distribution function and the probability negative binomial distribution function.

Above is a guide and some specific examples of using the NEGBINOM.DIST function in Excel.

Wishing you all success!