NEGBINOM.DIST Function

Basic Description

Given the probability of a success from a single event, the Excel NEGBINOM.DIST function calculates the probability mass function or the cumulative distribution function for the Negative Binomial Distribution. This gives the probability that there will be a given number of failures before a required number of successes is achieved. For example, given the the probability of 50%, that the toss of a coin will land on heads, the Negbinom.Dist function will calculate the probability that you will toss 10 tails before you toss 12 heads. The Negbinomdist.Dist function is new in Excel 2010, and so is not available in earlier versions of Excel. However the negative binomial probability mass function can be calculated by the Negbinomdist function, which is available in earlier versions of Excel.

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

where the arguments are as follows :

number_f The number of failures encountered before number_s successes
number_s The required number of successes
probability_s The probability of success in one trial
cumulative A logical argument that specifies the type of Negative Binomial Distribution to be calculated. This can be either:

TRUE use the cumulative distribution function
FALSE use the probability mass function

Note that, if the number_f and number_s arguments are input as decimals, these values are truncated to integers.

 

Negbinom.Dist Function Examples
Binomial Distribution Probability Mass Function Chart

Negative Binomial Distribution Function Showing the Probability of exactly x Tails Before 12 Heads from a Tossed Coin
Example 1 – Probability Mass Function

In the chart on the right, the Negative Binomial Probability Mass Function is used to show, for a tossed coin, the probability that exactly x tails will be tossed before the toss of a coin results in 12 tossed heads. The spreadsheets below show the Excel Negbinom.Dist function used to evaluate this function for three different values of x. The functions are displayed in the spreadsheet on the left and the results are shown in the spreadsheet on the right. Clearly, the probability of tossing a head on any one trial is 50%, so this is the value of the probability argument.

 Formulas:

Examples of use of the Excel Negbinom.Dist Function

 Results:

Excel Negbinom.Dist Function Results

 

Cumulative Binomial Distribution Function Chart

Cumulative Negative Binomial Distribution Function Showing the Probability of at most x Tails Before 12 Heads from a Tossed Coin
Example 2 – Cumulative Distribution Function

In the chart on the right, the Cumulative Negative Binomial Function is used to show the probability, for a tossed coin, that at most x tails will be tossed before the toss of a coin results in 12 tossed heads. The spreadsheets below show the Excel Negbinom.Dist function used to evaluate this function for three different values of x. Again, the functions are displayed in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:

Examples of use of the Excel Negbinom.Dist Function

 Results:

Excel Negbinom.Dist Function Results

 

 

Negbinom.Dist Function Errors

If you get an error from the Excel Negbinom.Dist Function, this is likely to be one of the following:

#NUM! Occurs if either the supplied number_f is < 0 or the supplied number_s is < 1 or the supplied probability_s is < 0 or > 1
#VALUE! Occurs if any of the number_f, number_s, or probability_s arguments are nonnumeric.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment