NEGBINOM.DIST

What it does

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

NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) evaluates the negative binomial distribution: the number of failures observed before a fixed number of successes is reached, in independent Bernoulli trials.

Writing x for the truncated number_f, r for the truncated number_s, p for probability_s and q = 1 − p:

Probability mass function (cumulative = FALSE) — the probability of exactly x failures before the r-th success:

f(x; r, p) = C(x + r − 1, x) · p^r · q^x
           = Γ(x + r) / ( Γ(r) · x! ) · p^r · q^x,      x = 0, 1, 2, …

The combinatorial factor counts the arrangements of x failures among the first x + r − 1 trials; the final trial is by construction the r-th success, which is why the binomial coefficient uses x + r − 1 and not x + r. Getting that off by one wrong is the classic error in implementing this distribution.

Cumulative distribution function (cumulative = TRUE) — the probability of at most x failures:

F(x; r, p) = Σ_{j=0}^{x} f(j; r, p)  =  I_p(r, x + 1)

where I_p(a, b) is the regularized incomplete beta function. That closed form (A&S 26.5.24 and the neighbouring relations) is what makes the CDF computable in constant time rather than as a sum, and it is the same machinery the binomial and F distributions bottom out in.

Domain and range. The documented domain is x ≥ 0 and r ≥ 1, both after truncation to integers, with 0 ≤ p ≤ 1. The mass is in [0, 1] and sums to 1 over x whenever p > 0.

Moments and limits.

mean     = r·q/p
variance = r·q/p²  =  mean / p          always ≥ mean: the distribution is overdispersed
f(0; r, p) = p^r
r = 1                    gives the geometric distribution on {0, 1, 2, …}
r → ∞, p → 1 with r·q/p → λ fixed   gives Poisson(λ)

The overdispersion is why the negative binomial is used at all: it is the count distribution for data whose variance exceeds its mean, where Poisson fails. It is also the Gamma–Poisson mixture — a Poisson whose rate is itself Gamma-distributed — which is the identity that gives it a continuous r in most statistical software.

The r question. Mathematically the density is written with Γ(r) and is perfectly well defined for any real r > 0; that generalization is what makes the Gamma–Poisson mixture work and is what R's dnbinom supports. Excel does not: number_s is documented as truncated to an integer and required to be at least 1. So Excel's NEGBINOM.DIST is the strictly integer-r special case, and a user fitting an overdispersed count model with a fractional dispersion parameter cannot express it here.

Degenerate corners. At p = 1 every trial succeeds, so f(0; r, 1) = 1 and f(x; r, 1) = 0 for x > 0. At p = 0 no success ever occurs and the distribution does not exist — the mass is zero for every finite x and the total is zero, not one. Microsoft's documented boundary is probability_s < 0, which admits p = 0; what the function returns there is a real question and is on the probe list.

Arguments

Microsoft's page gives four required arguments:

number_f and number_s are truncated to integers, per Microsoft's remark. probability_s is not truncated. cumulative has no default.

The pinned catalogue supplies NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) with four required arguments.

Try it

Quirks

The rename that added an argument

NEGBINOMDIST(x, r, p)   corresponds to   NEGBINOM.DIST(x, r, p, FALSE)

and there is no legacy spelling of the cumulative branch at all. Two practical consequences:

• A formula migrated by mechanically inserting dots produces a call with the wrong number of arguments, which fails at entry rather than silently.
• More insidiously, a reader who has internalized "the dotted name is the same function" will read NEGBINOM.DIST(x, r, p, TRUE) as if a legacy equivalent existed and reason about legacy behaviour that was never there.

And for the branch that does correspond, correspondence is a statement about signatures and mathematics, not about bits. Excel dispatches compatibility functions as their own surfaces; proving that NEGBINOMDIST(x, r, p) and NEGBINOM.DIST(x, r, p, FALSE) publish identical doubles requires evidence, and the evidence record attached to this page is explicit that the question of which negative binomial surface was even measured upstream is unresolved. See What has not been checked.

Result and edge cases

Returns Number.

• x = 0 gives p^r in both branches — the probability that the first r trials are all successes. Like the Poisson's k = 0 window, this is a route-blind input: the combinatorial factor is 1 and the q^x factor is 1, so every staging reduces to a power. It is the easiest input to get right and says the least about how the function is built.
• p = 1 collapses the distribution to a point mass at x = 0.
• p = 0 is admitted by the documented boundary and is mathematically degenerate; see above.
• Truncation before validation. number_f and number_s are truncated first, so NEGBINOM.DIST(0.9, 1.9, p, c) is the (0, 1) case, and number_s = 1.5 truncates to the admissible 1 rather than being rejected as non-integer.
• Large x and r. The binomial coefficient C(x+r−1, x) overflows binary64 long before the probability underflows; the two factors move in opposite directions and the naive product is unevaluable across a wide, ordinary band of inputs. See Numerical notes.
• CDF saturation. For x well above the mean the CDF rounds to exactly 1 and the upper tail is unrecoverable by subtraction. The incomplete-beta identity gives the complement directly: 1 − F(x; r, p) = I_q(x+1, r).
• Arrays. The recorded profile is Custom; elementwise lifting is not settled here.

Empty, missing and error arguments follow the shared call model; see Coercion and lifting.

Errors

As documented by Microsoft on the NEGBINOM.DIST page (retrieved for this page):

Three defects in that published text are worth recording rather than tidying away:

1. The probability row names an argument that does not exist. It reads "if probability_s < 0 or if probability > 1". There is no probability argument; the upper-bound condition is written against a name the syntax line never introduces. The intended reading is obvious and the published text is wrong.
2. p = 0 is admitted by the stated inequality — the row says < 0, not ≤ 0 — even though the distribution does not exist at p = 0. Either the boundary is deliberate and the function returns something at p = 0, or the documentation is loose. Nobody has checked.
3. The cumulative argument is described as returning "the probability density function" when FALSE. The negative binomial is a discrete distribution; what that branch returns is a probability mass function. This is the mirror image of a defect the Handbook records on NORM.S.DIST, where a continuous density is called a mass function. The two pages have the terminology exactly inverted, each in the wrong direction. The computations are not in dispute; the published descriptions of them are.

Error values in any argument propagate under the ordinary coercion rules.

How to implement

Numerical notes

1. The naive product is unevaluable over an ordinary band. C(x+r−1, x) · p^r · q^x pairs a combinatorial factor that overflows with probability factors that underflow. At r = 10, p = 0.01, x = 2000 the coefficient is astronomically large and p^r q^x astronomically small, while the answer is an unremarkable small probability. This is the same structural hazard as the Poisson's, and it is why every serious discrete-distribution implementation works in log space or better.

2. Log space fixes the range and creates the cancellation.

ln f = lnΓ(x+r) − lnΓ(r) − lnΓ(x+1) + r·ln p + x·ln q

is representable everywhere, but for x near the mean the terms are individually large and nearly cancel. An absolute error ε in ln f is a relative error ε in f, so the accuracy is set by how well the log-gamma differences are computed, not by the exponential. This is the lgamma composition the upstream record refers to when it describes the negative binomial as presumably sharing the binomial's route family "with different integer arguments".

3. The saddle-point route is the modern standard — and it was ruled out here. Loader's method (2000) writes the mass as an exponential of a Stirling correction and a cancellation-free deviance term, and it is what R's dnbinom uses. The evidence record attached to this page reports that Loader's dnbinom was refuted as a model of Excel's surface, and separately that this surface does not inherit the identified binomial route. Both are recorded upstream as candidate-model scores; the record's own reader warning states that neither figure may be attributed to NEGBINOM.DIST specifically. What survives as a Handbook-safe statement is qualitative and still useful: two strong hypotheses about how Excel computes this distribution have been eliminated, and no replacement has been identified.

4. The CDF should be an incomplete beta, not a sum. F(x; r, p) = I_p(r, x+1) costs O(1) through a continued fraction and accumulates one rounding rather than x. The standard implementation is the Lentz-evaluated continued fraction with the I_p(a,b) = 1 − I_q(b,a) symmetry used to keep the argument on the fast-converging side of (a)/(a+b) — the treatment in Numerical Recipes §6.4 and, at reference quality, DiDonato and Morris's TOMS algorithms. Summing the mass terms instead is both slower and less accurate, and it inherits every problem of section 1.

5. The recurrence. f(x) = f(x−1) · q · (x + r − 1)/x is exact in structure and cheap, and running it upward from f(0) = p^r is stable while the terms increase. It is the natural way to produce a whole table of masses, and it is a good cross-check on a saddle-point implementation — though it cannot start where p^r underflows.

6. p^r deserves its own attention. At x = 0 the entire answer is p^r with integer r. Whether that is computed by pow, by repeated multiplication, or by exp(r ln p) is directly observable at that one input, and the three disagree in the last bits. It is the same one-cell-diagnoses-the-constant situation that PHI(0) provides for the normal density.

What a careful independent implementation does: saddle-point mass with a tabulated Stirling correction; incomplete-beta CDF by continued fraction with the symmetry switch; explicit handling of x = 0, p = 1 and the p = 0 degeneracy; and a stated bound. See About implementation options.

Rust code

79 negbinom_dist "NEGBINOM.DIST" (4, 4) [V "number_f", V "number_s", V "probability_s", V "cumulative"] |s, a, sv| {
    let [f, n, p] = nums::<3>(s, a)?;
    out(s, dd::negbinom_dist_kernel(f, n, p, flag(s, a, 3)?))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• NEGBINOMDIST is the legacy spelling with three arguments — see the rename that added an argument above. This is one of the clearest cases in the statistical category that a dotted name is not always a rename.
• BINOM.DIST is the conjugate question: the binomial fixes the number of trials and counts successes; the negative binomial fixes the number of successes and counts failures. The two CDFs are two faces of the same incomplete beta, which is why one might expect them to share a route — and why the upstream finding that this surface does not inherit the binomial's identified route is a genuine result rather than an absence of one.
• GEOMETRIC does not exist in Excel as its own function; the geometric distribution is NEGBINOM.DIST(x, 1, p, cumulative).
• POISSON.DIST is the limit at large r, and the negative binomial is the Gamma–Poisson mixture that generalizes it to overdispersed counts.
• BETA.DIST carries the same regularized incomplete beta from the continuous side: NEGBINOM.DIST(x, r, p, TRUE) = BETA.DIST(p, r, x+1, TRUE). Two Excel surfaces, one number — a free consistency oracle, and the analogue of the Poisson/chi-square identity.
• Confused with: the "number of trials" parameterization used by some textbooks and by several other statistical packages, in which the variable counts trials rather than failures and everything shifts by r. Excel counts failures. A formula ported from another system without checking this is off by r and will still look plausible.

Named variants

No named variants are recorded yet.