NEGBINOM.DIST function
NEGBINOM.DIST returns the negative binomial probability of seeing a set number of failures before a target number of successes. An example is how many cold calls fail before you land five meetings.
Example
Read it as a sentence: find the exact chance of the number in D1 failures before the 5th success when each try has a 0.25 chance of success, which gives 0.000976563.
Excel shows 0.000976563
D
1
Data
Syntax
NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)
-
number_f(required) - Number of failures before the target is reached.
-
number_s(required) - The target number of successes.
-
probability_s(required) - Chance of success on each independent trial, between 0 and 1.
-
cumulative(required) - TRUE for the chance of that many failures or fewer, FALSE for exactly that many.
Returns: The negative binomial probability.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=NEGBINOM.DIST(10,5,0.25,FALSE) | 0.05504866 | Working example |
=NEGBINOM.DIST(10,5,0.25,TRUE) | 0.313514058 | Other cumulative flag |
=NEGBINOM.DIST(10,5,0.25,0) | 0.05504866 | Cumulative as 0 |
=NEGBINOM.DIST("10",5,0.25,FALSE) | 0.05504866 | Text-number in first arg |
=NEGBINOM.DIST({10,10},5,0.25,FALSE) | 0.05504866 | Array in first arg (lifts, spills) |
=NEGBINOM.DIST(Data!D1,5,0.25,FALSE) | 0.000976563 | Blank cell as first arg |
=NEGBINOM.DIST("abc",5,0.25,FALSE) | #VALUE! | Non-numeric text in first arg |
=NEGBINOM.DIST(NA(),5,0.25,FALSE) | #N/A | Error in first arg |
The Data sheet these formulas read
A cell that starts with = holds that formula.
D
1
Data
Watch out for
- Failures and successes are truncated to whole numbers.
- probability_s outside 0 to 1 gives #NUM!.
Where it works
- Excel
- NEGBINOM.DIST arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .