# A to Z of Excel Functions: The NEGBINOM.DIST Function

17 October 2022

*Welcome
back to our regular A to Z of Excel Functions blog. Today we look at the NEGBINOM.DIST function. *

** **

**The
NEGBINOM.DIST function**

In probability theory and statistics, the negative
binomial distribution is a discrete probability distribution that models the
number of successes in a sequence of **n **independent success / failure experiments, each of which yields success with
probability **p, **before a specified number of failures (denoted **r**) occur. For the record, a
success / failure experiment is also called a Bernoulli experiment or Bernoulli
trial.

This function returns the negative
binomial distribution, the probability that there will be a **number_f **failures before the **number_s**-th success, with **probability_s **probability of a success. The **NEG****BINOM.DIST **function should be used in
problems with a fixed number of successes, when the outcomes of any trial are
only success or failure, when trials are independent and when the probability
of success is constant throughout the experiment. For example, **NEG****BINOM.DIST** can calculate the probability that two babies born are
male before one female baby is born.

The **NEG****BINOM.DIST **function employs the following syntax to operate:

**NEGBINOM.DIST(number_f,
number_s, probability_s, cumulative)**

The **NEG****BINOM.DIST **function has the following arguments:

**number_f:**this is required and represents the number of failures in trials**number_s:**this is also required. This is the threshold number of successes**probability_s:**again, this is required. This is the probability of success in each trial**cumulative:**the final argument is also required. This is a logical value that determines the form of the function. If**cumulative**is TRUE, then**NEG****BINOM.DIST**returns the cumulative distribution function, which is the probability that there are at most**number_f**failures; if**cumulative**is**FALSE**, it returns the probability density function, which is the probability that there are**number_f**failures.

It should be further noted that:

**number_f**and**number_s**are truncated to integers- if
**number_f**,**number_s**, or**probability_s**is / are non-numeric,**NEGBINOM.DIST**returns the*#VALUE!*error value - if
**number_f**< 0 or**number_s**< 1,**NEGBINOM.DIST**returns the*#NUM!*error value - if
**probability_s**< 0 or**probability_s**> 1,**NEGBINOM.DIST**returns the*#NUM!*error value - the equation for the negative binomial distribution is:

where:

**x** is **number_f**, **r** is **number_s** and **p** is **probability_s**.

Have I lost you yet? Please see my example below:

Essentially, **NEG****BINOM.DIST **replaces **NEG****BINOMDIST.** First introduced in Excel 2010, this provides
more compatibility with other statistical software. The former function is still recognised in
Excel though for legacy reasons.

*We’ll continue our A
to Z of Excel Functions soon. Keep
checking back – there’s a new blog post every other business day.*

*A full page of the function articles can be found **here.*