Check Sheet Logo
Check Sheet


The NEGBINOMDIST function calculates the probability of having a certain number of failures (num_failures) before a certain number of successful outcomes (num_successes) occur, given the probability of success in a single trial (prob_success). This function is often used in statistical analysis to model the number of failures before a certain number of successes in a sequence of independent and identically distributed Bernoulli trials.


Use the NEGBINOMDIST formula with the syntax shown below, it has 3 required parameters:

=NEGBINOMDIST(num_failures, num_successes, prob_success)
  1. num_failures (required):
    The number of failures before the desired number of successful outcomes.
  2. num_successes (required):
    The desired number of successful outcomes.
  3. prob_success (required):
    The probability of success in a single Bernoulli trial. Must be between 0 and 1, inclusive.


Here are a few example use cases that explain how to use the NEGBINOMDIST formula in Google Sheets.

Modeling the number of defective items in a batch

Suppose a factory produces a batch of 20 items, and the probability of any item being defective is 0.1. We can use NEGBINOMDIST to calculate the probability of having 2 defective items before the 10th non-defective item. This can help the factory estimate the likelihood of producing batches with a certain number of defects.

Calculating the expected number of trials

We can use NEGBINOMDIST to calculate the expected number of Bernoulli trials necessary to achieve a certain number of successful outcomes, given the probability of success. This can be useful in designing experiments or estimating the resources required to achieve a certain goal.

Analyzing the number of attempts to win a game

Suppose a player has a 20% chance of winning a game, and they must win 3 games before losing 2 games. We can use NEGBINOMDIST to calculate the probability of the player winning on their 7th attempt. This can help the player estimate their chances of winning and decide whether to continue playing.

Common Mistakes

NEGBINOMDIST not working? Here are some common mistakes people make when using the NEGBINOMDIST Google Sheets Formula:

Incorrect order of arguments

One of the most common mistakes when using NEGBINOMDIST is to enter the arguments in the wrong order. It's important to ensure that num_failures is the first argument, followed by num_successes, and then prob_success.

Incorrect probability value

Another common mistake is to enter an incorrect value for prob_success. This value should be between 0 and 1, inclusive. Double check that you have entered the correct value.

Non-integer arguments

NEGBINOMDIST requires integer values for num_failures and num_successes. If you enter non-integer values, you will get an error. Make sure you round your numbers or use the INT function to convert them to integers.

Using the wrong distribution

Make sure you are using the NEGBINOMDIST function for negative binomial distributions. If you are working with a different distribution, you will need to use a different function.

Incorrectly interpreting the output

The output of NEGBINOMDIST is the probability of getting num_successes successes before num_failures failures. Make sure you understand what this means and how to interpret the result.

The following functions are similar to NEGBINOMDIST or are often used with it in a formula:


    The BINOMDIST function calculates the probability of a certain number of successes in a fixed number of trials given a constant probability of success for each trial. This function is commonly used in statistical analysis and hypothesis testing.


    The POISSON function returns the Poisson distribution probability density function, which is used to show the probability of a certain number of events occurring in a fixed interval of time or space. It takes in the values for the number of events (x), the mean (mean), and a boolean for whether or not to return the cumulative distribution (cumulative).

  • PROB

    The PROB function calculates the probability that a value lies between two limits based on a range of data and its corresponding probabilities. This function is commonly used in statistics to calculate the probability distribution of a set of data.


    The STDEV.P function is a statistical function that calculates the standard deviation of a population based on a sample of numerical data. It is commonly used to measure the amount of variation or dispersion in a dataset. The formula assumes that the input values represent the entire population, rather than a sample. If the input values represent a sample, you should use the STDEV.S function instead.

  • VAR.P

    The VAR.P function returns the variance of its arguments, which is a measure of the spread of a dataset in statistics. Unlike the VAR function, VAR.P function considers all the values in the dataset when calculating its variance, not just a sample of them. The function is commonly used to analyze the variability of a dataset and determine how much the data points deviate from the mean.

Learn More

You can learn more about the NEGBINOMDIST Google Sheets function on Google Support.