Documentation/Calc Functions/NEGBINOM.DIST
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
NEGBINOM.DIST
Category:
Statistical Analysis
Summary:
Calculates negative binomial distribution probabilities from either the probability mass function or the cumulative distribution function.
The negative binomial distribution is a discrete probability distribution that is used to analyze data in many domains. It differs from the binomial distribution in that the number of successes is fixed, and the number of independent trials is variable. The first argument to NEGBINOM.DIST assumes that the random variable is defined as the number of failures occurring prior to the r-th success (other sources may define the random variable differently, for example it could be the number of trials needed to obtain the r-th success). The function calculates the probability that there will be x failures before the r-th success, with a constant probability p of a success.
Syntax:
NEGBINOM.DIST(X; R; SP; Cumulative)
Returns:
Returns a real number in the range [0, 1], which is the negative binomial distribution probability for the given arguments.
Arguments:
X is a non-negative integer, or a reference to a cell containing that integer, that is the number of trial failures occurring prior to the R-th success.
R is a positive integer, or a reference to a cell containing that integer, that is the threshold number of trial successes.
SP is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, that is the probability of a successful outcome on each trial. As a probability, SP lies in the range [0, 1] (or equivalently 0% ≤ SP ≤ 100%).
Cumulative is a logical value, or a reference to a cell containing that value, that determines whether the required probability is taken from the probability mass function or the cumulative distribution function. If Cumulative is set to 0 or FALSE, a value from the probability mass function is calculated. For any other values of Cumulative, a value from the cumulative distribution function is calculated.
- If any of X, R, or SP is non-numeric, then NEGBINOM.DIST reports a #VALUE! error.
- If either X or R is a non-integer value, then NEGBINOM.DIST truncates it to an integer value.
- If X is less than 0, then NEGBINOM.DIST reports an invalid argument error (Err:502).
- If R is less than 1, then NEGBINOM.DIST reports an invalid argument error (Err:502).
- If SP is less than 0.0 or greater than 1.0, then NEGBINOM.DIST reports an invalid argument error (Err:502).
Additional details:
- Requirements for NEGBINOM.DIST are not included in ODF 1.2 and the function is provided for interoperability with Microsoft Excel.
- When the Cumulative argument is set to 0 or FALSE, the following relationship exists between the NEGBINOM.DIST and NEGBINOMDIST functions:
- [math]\displaystyle{ \text{NEGBINOM.DIST}(x;\:r;\:p;\:0)~=~\text{NEGBINOMDIST}(x;\:r;\:p) }[/math]
- NEGBINOMDIST does not calculate probabilities from the cumulative distribution function of the negative binomial distribution.
- The formula for NEGBINOM.DIST when Cumulative is set to 0 or FALSE (probability mass function) is:
- [math]\displaystyle{ \begin{align} \text{NEGBINOM.DIST}(x;\:r;\:p;\:0)~&=~\binom{x\:+\:r\:-\:1}{r-1}\:\times\:(1\:-\:p)^{x}\:\times\:p^{r}\\&=~\frac{(x\:+\:r\:-\:1)!}{(r\:-\:1)!\:x!} \times\:(1\:-\:p)^{x}\:\times\:p^{r}\\ \end{align} }[/math]
- The formula for NEGBINOM.DIST when Cumulative is set to any value other than 0 and FALSE (cumulative distribution function) is:
- [math]\displaystyle{ \begin{align} \text{NEGBINOM.DIST}(x;\:r;\:p;\:1)~&=\sum_{i=0}^{x}\text{NEGBINOM.DIST}(i;\:r;\:p;\:0)\\ &=~\sum_{i=0}^{x}\binom{i\:+\:r\:-\:1}{r-1}\:\times\:(1\:-\:p)^{i}\:\times\:p^{r}\\&=~\sum_{i=0}^{x}\frac{(i\:+\:r\:-\:1)!}{(r\:-\:1)!\:i!} \times\:(1\:-\:p)^{i}\:\times\:p^{r}\\ \end{align} }[/math]
- The name space for NEGBINOM.DIST is
COM.MICROSOFT.NEGBINOM.DIST
.
- For more information on the negative binomial distribution, visit Wikipedia's Negative binomial distribution page.
Examples:
Formula | Description | Returns |
---|---|---|
=NEGBINOM.DIST(A1; A2; A3; A4) where cells A1:A4 contain the number 5, the number 2, the formula =1/6 , and the number 0 respectively. |
Suppose we repeatedly roll a single fair (unbiased) die and consider a successful trial outcome to be when we roll a 6. This outcome occurs with a probability of 1/6 on each trial. What is the probability of observing the second success on the seventh trial? Here the function calculates the probability as 6.70%. Note that the first argument is set to the value 5, which equates to the total trials required (7) minus the specified number of successes (2). | 0.0669795953360768 |
=NEGBINOM.DIST(0; 2; 1/6; 0) | Similar to the previous example, except here the formula calculates the probability of observing the second success on the second trial. The value returned is 2.78% and this is equal to [math]\displaystyle{ \left( \frac{1}{6} \right)^2 }[/math]. | 0.0277777777777778 |
=NEGBINOM.DIST(3; 2; 1/6; 1) | Similar to the previous example, except here the formula uses the cumulative distribution function to calculate the probability of the second success occurring on either the second, third, fourth, or fifth trial, returning 19.62%. The formula =NEGBINOM.DIST(0; 2; 1/6; 0) + NEGBINOM.DIST(1; 2; 1/6; 0) + NEGBINOM.DIST(2; 2; 1/6; 0) + NEGBINOM.DIST(3; 2; 1/6; 0) returns the same value. |
0.196244855967078 |
=NEGBINOM.DIST(5; 5; 50%; 0) | Suppose that we flip a fair (unbiased) coin. For each flip, the result will be either a head or a tail, and each of these will occur with an equal probability of 0.5 or 50%. Here the function calculates the probability that the fifth head will occur on the tenth flip, returning the value 12.30%. | 0.123046875 |
=NEGBINOM.DIST(5; 5; 50%; TRUE()) | Similar to the previous example, except that here the formula uses the cumulative distribution function to calculate the probability that the fifth head will occur on either the fifth, sixth, seventh, eighth, ninth, or tenth flip, returning the value 62.30%. The formula =NEGBINOM.DIST(0; 5; 50%; FALSE()) + NEGBINOM.DIST(1; 5; 50%; FALSE()) + NEGBINOM.DIST(2; 5; 50%; FALSE()) + NEGBINOM.DIST(3; 5; 50%; FALSE()) + NEGBINOM.DIST(4; 5; 50%; FALSE()) + NEGBINOM.DIST(5; 5; 50%; FALSE()) returns the same value. |
0.623046875 |
Related LibreOffice functions:
ODF standard:
None
Related (or similar) Excel functions:
NEGBINOM.DIST