The NEGBINOM.DIST function in Google Sheets is used to calculate the negative binomial distribution, which models the number of failures before a specified number of successes occurs in a sequence of Bernoulli trials. This function is particularly useful in statistical analyses involving discrete data, providing insights into probabilities in various practical applications, such as quality control and reliability testing.

Syntax

NEGBINOM.DIST(number_of_failures, number_of_successes, probability_of_success, cumulative) 

• number_of_failures: The total number of failures before achieving the specified number of successes.
• number_of_successes: The specified number of successes sought in the experiment.
• probability_of_success: The probability of success on each individual trial.
• cumulative: A boolean value (TRUE or FALSE) that determines the form of the function; TRUE returns the cumulative distribution function, while FALSE returns the probability mass function.

Example #1

NEGBINOM.DIST(3, 2, 0.5, FALSE) 

This function calculates the probability of obtaining exactly 3 failures before achieving 2 successes when the probability of success on each trial is 0.5. The result for these values is approximately 0.11719.

Example #2

NEGBINOM.DIST(4, 3, 0.3, TRUE) 

In this instance, the function determines the cumulative probability of experiencing up to 4 failures before recording 3 successes, under the assumption that each trial has a 30% chance of success. The result will be around 0.69657.

Example #3

NEGBINOM.DIST(5, 1, 0.2, FALSE) 

Here, the function computes the probability of having 5 failures before achieving the first success, with a 20% chance of success for each trial. The result is approximately 0.26214.

Error handling

• NUM!: This error occurs if number_of_failures or number_of_successes is non-integer or negative.
• VALUE!: This indicates that probability_of_success is not a valid number between 0 and 1.
• DIV/0!: This happens if probability_of_success equals 0 and cumulative is TRUE, as the function cannot compute the results in such scenarios.

Conclusion

The NEGBINOM.DIST function is a powerful tool for performing statistical calculations related to negative binomial distributions. By providing key insights into the probabilities associated with a specified number of failures and successes, it supports data-driven decision-making in numerous fields, from healthcare to finance. Mastering this function enhances analytical capabilities and enriches the understanding of variable relationships in statistical contexts.