How to Use the NEGBINOMDIST Function in Excel
Today, we’ll explore the NEGBINOMDIST function, a robust statistical tool available in both MS Excel and Google Sheets. This function is essential for computing the negative binomial distribution, which is the probability of a specified number of failures occurring before a predetermined number of successes, based on the given success probability for each trial.
Syntax
The syntax for the NEGBINOMDIST function is as follows:
NEGBINOMDIST(number_f, number_s, probability_s, cumulative)number_f: This is the number of failures for which the probability will be calculated.number_s: This is the threshold number of successes that must be reached.probability_s: This represents the probability of a success in each individual trial.cumulative: A Boolean value that specifies the function mode. If set to TRUE, which is the default setting, the function will return the cumulative distribution function. If FALSE, it returns the probability mass function.
Examples
Calculating Negative Binomial Distribution Probability
Let’s examine how the NEGBINOMDIST function can be applied in both MS Excel and Google Sheets with an example.
MS Excel
Imagine you are tasked with finding the probability of encountering 3 failures before achieving 5 successes, within 10 trials, with each trial having a success probability of 0.3.
| number_f | number_s | probability_s | Result |
|---|---|---|---|
| 3 | 5 | 0.3 | =NEGBINOMDIST(3, 5, 0.3, TRUE) |
This formula will yield the probability of accruing 3 failures before the 5th success transpires over 10 trials with a 0.3 chance of success in each trial.
Google Sheets
The application looks identical in Google Sheets:
| number_f | number_s | probability_s | Result |
|---|---|---|---|
| 3 | 5 | 0.3 | =NEGBINOMDIST(3, 5, 0.3, TRUE) |
Similarly, this function will compute the probability of observing 3 failures before 5 successes across 10 trials, each with a success probability of 0.3, in Google Sheets.
This overview guides you through using the NEGBINOMDIST function to determine negative binomial distributions in both MS Excel and Google Sheets, making it an invaluable resource for anyone conducting statistical analyses or working with probabilities.
More information: https://support.microsoft.com/en-us/office/negbinomdist-function-f59b0a37-bae2-408d-b115-a315609ba714