Binomial Distribution in Excel: BINOM.DIST With Examples

To work with a binomial distribution in Excel, use BINOM.DIST: it returns the chance of getting a certain number of successes in a fixed number of tries. You give it the number of successes, the number of trials, the chance of success on each trial, and TRUE or FALSE. This guide is for students and analysts who need exact, "or fewer", "at least" and "between" probabilities, with worked examples you can check.

What is a binomial distribution?#

A binomial distribution gives the probability of each possible number of successes when you repeat the same yes-or-no trial a fixed number of times. It fits when four things are true:

1. There is a fixed number of trials, called n (for example, 10 sales calls).
2. Each trial has two outcomes: success or failure (a sale or no sale).
3. The chance of success, called p, is the same on every trial (0.3, or 30%).
4. The trials are independent: one result doesn't change the next.

Coin tosses, faulty parts in a batch and people who reply to an email all fit this pattern. If p changes from trial to trial, or the trials affect each other, the binomial model is the wrong tool.

How to use BINOM.DIST in Excel#

BINOM.DIST has four arguments, all required:

=BINOM.DIST(number_s, trials, probability_s, cumulative)

A salesperson makes 10 calls and each call has a 0.3 chance of a sale. The chance of exactly 3 sales is:

=BINOM.DIST(3,10,0.3,FALSE)

The result is about 0.2668, or 26.7% with Percent format. BINOM.DIST arrived in Excel 2010 and works in every later version, including Microsoft 365 and Excel for Mac. The older BINOMDIST still works and gives the same answers.

Exact vs cumulative probability#

The last argument changes the question. FALSE asks "exactly k?", and TRUE asks "k or fewer?".

=BINOM.DIST(3,10,0.3,TRUE)

This returns about 0.6496: the chance of 0, 1, 2 or 3 sales added together. Most "at least" and "no more than" questions use the cumulative version:

Watch the word "fewer than": fewer than 3 means 2 or fewer, so the first argument is 2. "At least 5" is everything except 0 to 4, so you subtract the cumulative result for 4 from 1.

How to find the probability between two values#

BINOM.DIST.RANGE returns the chance that the successes fall between two numbers, inclusive. It needs Excel 2013 or later, and its argument order is different from BINOM.DIST:

=BINOM.DIST.RANGE(10,0.3,2,4)

Trials come first, then the probability, then the lower and upper counts. The result is about 0.7004, the chance of 2, 3 or 4 sales. In older versions, subtract two cumulative results instead: =BINOM.DIST(4,10,0.3,TRUE)-BINOM.DIST(1,10,0.3,TRUE) gives the same answer.

To go the other way, BINOM.INV returns the smallest number of successes whose cumulative probability reaches a target. =BINOM.INV(10,0.3,0.9) returns 5, because 4 or fewer covers only 0.8497 and 5 or fewer covers 0.9527.

How to build a binomial distribution table#

A full table shows the chance of every possible result. Put n and p in their own cells so you can change them later:

Type 0 to 10 in A5:A15, then in B5:

=BINOM.DIST(A5,$B$1,$B$2,FALSE)

Fill it down to B15 (Windows: Ctrl+D; Mac: ⌘D). The $ signs lock B1 and B2, so every row uses the same n and p while A5 changes to A6, A7 and so on. Without them, the formula would slide down to empty cells and return wrong results. The guide to the dollar sign in Excel formulas explains this in detail.

The probabilities in B5:B15 add up to 1. Select A4:B15 and insert a column chart to see the shape: it peaks at 3 and tails off to the right. The guides to making a histogram and building a frequency table show how to compare this shape with real data.

Mean and standard deviation of a binomial distribution#

You don't need a function for these: they are short formulas. The mean, or expected number of successes, is n times p, and the standard deviation is the square root of n × p × (1 − p):

=B1*B2
=SQRT(B1*B2*(1-B2))

With n = 10 and p = 0.3, the mean is 3 sales and the standard deviation is about 1.449. The guide to standard deviation in Excel explains what that spread means.

If you need the formula behind BINOM.DIST, it is COMBIN(n, k) × p^k × (1 − p)^(n − k). So =COMBIN(10,3)*0.3^3*0.7^7 also returns 0.2668. For zero successes it shrinks to (1 − p)^n, which is why "at least one" is =1-POWER(1-p,n).

Common BINOM.DIST mistakes#

Most wrong answers come from the question, not the function:

• Using FALSE for an "or fewer" question. FALSE returns only one bar of the chart. Use TRUE for cumulative questions.
• Off-by-one in "at least". At least 5 is 1-BINOM.DIST(4,...), not 1-BINOM.DIST(5,...).
• p written as a whole number. 30 instead of 0.3 returns #NUM!. Probabilities must be between 0 and 1.
• More successes than trials. =BINOM.DIST(12,10,0.3,FALSE) returns #NUM!.
• Mixing up BINOM.DIST.RANGE. Its trials and probability come first, before the counts.

Practise it#

Every probability table depends on two basics: writing a formula correctly and locking the right cells. Practise your first formulas in a live spreadsheet, then practise absolute references with $ signs so your tables fill down without errors. Or try the exercise on this page and work out the chance of at least one faulty part.