BINOM.DIST: Binomial Probability Distribution Made Easy

Home » pub » Tutorials » BINOM.DIST: Binomial Probability Distribution Made Easy

Master the BINOM.DIST function in Excel to calculate probabilities for a fixed number of independent trials with two possible outcomes, such as success or failure. This tutorial explains the BINOM.DIST syntax, number of trials, probability of success, exact versus cumulative probabilities, practical examples, and common errors. You’ll also learn how to apply binomial distribution to quality control, sales forecasting, survey analysis, risk assessment, and other real-world probability problems. Ideal for students, analysts, statisticians, researchers, finance professionals, and Excel users working with probability and statistical models.

You run a process that either passes or fails, and you want the odds. What is the chance that exactly 3 of 20 parts are defective? Or that a salesperson closes at least 8 of 15 calls? Any time you have a fixed number of yes-or-no trials, one distribution fits: the binomial. Excel packs it into the BINOM.DIST function. It takes your number of successes, the number of trials, and the success rate, then returns the exact probability. With one cumulative switch, it also gives you the chance of that many successes or fewer.

This guide explains the binomial distribution in plain terms. First, it covers the four conditions that must hold. Then it walks through the arguments and the vital cumulative switch. Seven worked examples and a full troubleshooting section follow. By the end, you will answer real yes-or-no probability questions with confidence.

When the Binomial Distribution Applies

The binomial distribution fits a very specific setup. You need a fixed number of trials. Each trial must have just two outcomes, often called success and failure. The success rate must stay the same across trials. Finally, the trials must be independent. The infographic below shows a typical binomial shape.

Coin flips are the classic example. Each flip is heads or tails, the odds stay fixed, and one flip does not affect the next. Similarly, testing parts for defects fits well. Each part passes or fails at a steady rate. Consequently, the binomial models both cases neatly.

The Four Conditions

Before you trust a binomial result, check these four rules. They define whether the model is valid. Breaking one of them can make the answer misleading. So it is worth a quick review each time.

The binomial checklist: 1. FIXED trials the number n is set in advance. 2. TWO outcomes each trial is success or failure. 3. CONSTANT rate the success chance p never changes. 4. INDEPENDENT one trial does not affect another. If all four hold, BINOM.DIST is the right tool. If the rate drifts or trials depend on each other, the binomial no longer fits and results mislead.

The Syntax and the Cumulative Switch

BINOM.DIST takes four arguments. The first three describe your scenario. The fourth is a simple switch that changes the whole meaning. Getting that switch right is the most important skill here.

Syntax: =BINOM.DIST(number_s, trials, probability_s, cumulative) number_s -> the number of successes you ask about. trials -> the total number of trials (n). probability_s -> the success rate per trial (p). cumulative -> TRUE = P(X <= number_s), a running total. FALSE = P(X = number_s), an exact count. Example: =BINOM.DIST(6, 10, 0.5, FALSE) -> exactly 6 heads. =BINOM.DIST(6, 10, 0.5, TRUE) -> 6 heads or fewer.

FALSE means exactly, TRUE means at most. Use FALSE for the chance of a precise count. Use TRUE for the chance of that count or anything lower. This single switch is the source of most confusion, so read it carefully each time.

Example 1: The Exact Probability

Start with the simplest question. What is the chance of an exact number of successes? You set cumulative to FALSE. This gives the probability of that count alone.

Exactly 3 defects in 20 parts: A process has a 10% defect rate. You test 20 parts. =BINOM.DIST(3, 20, 0.10, FALSE) Result: about 0.1901, or 19.01%. Reading it: there is roughly a 19% chance of finding exactly 3 defective parts in a batch of 20.

Example 2: The Cumulative Probability

Often you care about a threshold, not an exact count. What is the chance of 3 defects or fewer? Here you switch cumulative to TRUE. This adds up the probabilities from 0 through 3.

3 defects or fewer: Same setup: 20 parts, 10% defect rate. =BINOM.DIST(3, 20, 0.10, TRUE) Result: about 0.8670, or 86.70%. Meaning: there is about an 87% chance of getting 3 or fewer defects. The cumulative version sums P(0) + P(1) + P(2) + P(3) for you.

Example 3: At Least X Successes

Sometimes you want the opposite tail. What is the chance of at least a certain number? You use the complement rule. Because the total probability is 1, you subtract the cumulative value.

At least 8 sales in 15 calls: A rep closes 40% of calls. They make 15 calls. "At least 8" means "not 7 or fewer", so: =1 - BINOM.DIST(7, 15, 0.40, TRUE) Result: about 0.0905, or 9.05%. The trick: subtract the cumulative up to one below your target. This flips a "3 or fewer" tool into an "at least 8" answer.

Example 4: The Probability of a Range

You may want the chance of a range, such as 5 to 8 successes. There is a dedicated function for this. BINOM.DIST.RANGE handles it directly. Alternatively, you can subtract two cumulative values.

Between 5 and 8 successes: Direct function: =BINOM.DIST.RANGE(15, 0.40, 5, 8) (trials, rate, lower, upper) Or with two cumulative calls: =BINOM.DIST(8,15,0.40,TRUE) - BINOM.DIST(4,15,0.40,TRUE) Both give the chance of 5, 6, 7, or 8 successes. The RANGE function is cleaner when you want a band.

Example 5: The Mean and Spread

A binomial distribution has a simple mean and spread. The mean is trials times the success rate. The variance is that mean times the failure rate. These quick formulas help you sense-check a result.

Expected value and variation: For n trials with success rate p: mean = n * p variance = n * p * (1 - p) std dev = SQRT(n * p * (1 - p)) Example: 20 parts, 10% defect rate. mean = 20 * 0.10 = 2 defects expected std dev = SQRT(20 * 0.10 * 0.90) = 1.34 So you expect about 2 defects, give or take 1.3.

Example 6: Find a Threshold with BINOM.INV

Sometimes you want to work backward. You have a probability target and want the matching count. BINOM.INV does this. It returns the smallest number of successes where the cumulative probability meets your target.

Set an acceptance threshold: =BINOM.INV(trials, probability_s, alpha) Example: 20 parts, 10% defect rate, alpha = 0.95. =BINOM.INV(20, 0.10, 0.95) -> 4 Meaning: 95% of the time you see 4 or fewer defects. So a batch with 5 or more defects is unusual. This is useful for setting quality-control limits.

Example 7: Build a Full Distribution Table

To see the whole picture, list every outcome. You place the counts in a column. Then you fill a BINOM.DIST formula down beside them. This produces the table behind the bar chart.

Successes k

P(X = k)

P(X <= k)

0.122

0.270

0.392

0.285

0.677

The second column uses FALSE for each exact chance. The third uses TRUE for the running total. Together they show how probability builds up across outcomes. This table is also the source data for a clear bar chart. Plotting it turns the numbers into an instant visual.

Troubleshooting BINOM.DIST

All three problems below are the most common. Each has a clear cause and a quick fix.

You get a #NUM! error

This error means an argument sits out of range. The number of successes cannot exceed the number of trials, so a count larger than n fails. Check that number_s is no more than trials. The success rate must also sit between 0 and 1, because it is a probability. A value like 40 instead of 0.40 breaks it, so convert any percentage to a decimal. Both the successes and trials should be whole numbers too. Once every argument sits in its valid range, the error clears.

The probability seems far too high or low

A surprising result usually traces to the cumulative switch. Setting it to TRUE gives the chance of that count or fewer, while FALSE gives only the exact count. These two values differ a lot, so the wrong switch produces a confusing answer. Decide first whether you want an exact probability or a running total. Another common slip is entering the success rate as a percentage rather than a decimal. Confirm the switch and the rate format, and the probability returns to a sensible value.

The binomial does not match reality

If binomial predictions seem off from real data, one of the four conditions may be broken. The most common culprit is a success rate that is not truly constant, or trials that are not independent. For example, sampling without replacement from a small group changes the odds each draw. In that case a different model, the hypergeometric distribution, fits better. Review whether your trials really are independent with a fixed rate. When they are not, the binomial assumptions no longer hold and the numbers will drift from observation.

Frequently Asked Questions

What does the BINOM.DIST function calculate?+

Essentially, BINOM.DIST calculates the probability of a given number of successes in a fixed number of independent yes-or-no trials, each with the same success rate. You supply the number of successes, the number of trials, and the success rate per trial. The fourth argument, cumulative, controls what it returns: FALSE gives the probability of exactly that many successes, while TRUE gives the probability of that many or fewer. For example, =BINOM.DIST(3, 20, 0.10, FALSE) returns the chance of exactly 3 defects in 20 parts at a 10% defect rate. It suits any pass-or-fail situation with a steady rate.
What is the difference between cumulative TRUE and FALSE?+

Specifically, the cumulative argument changes the entire meaning of the result. When set to FALSE, BINOM.DIST returns the probability of exactly the number of successes you specified, a single point on the distribution. When set to TRUE, it returns the cumulative probability of that number of successes or fewer, adding up every outcome from zero up to your value. So FALSE answers "what is the chance of exactly 3?" while TRUE answers "what is the chance of 3 or fewer?" These two numbers differ substantially, which makes the switch the most common source of mistakes with this function.
How do I calculate "at least" a number of successes?+

Because BINOM.DIST naturally handles "at most" through the cumulative option, you find "at least" by using the complement. Since all probabilities sum to 1, the chance of at least k successes equals 1 minus the chance of k minus 1 or fewer. In Excel, that is =1 - BINOM.DIST(k-1, trials, rate, TRUE). For example, the chance of at least 8 sales in 15 calls at a 40% rate is =1 - BINOM.DIST(7, 15, 0.40, TRUE). This subtraction trick flips the cumulative "or fewer" logic into an "at least" answer without any extra functions.
When should I not use the binomial distribution?+

Avoid the binomial distribution when its four conditions are broken. In particular, it requires a fixed number of trials, exactly two outcomes, a constant success rate, and independent trials. If the success rate drifts over time or the trials influence each other, the model no longer fits. A common example is sampling without replacement from a small population, where each draw changes the remaining odds; there the hypergeometric distribution is more appropriate. Similarly, if you are counting rare events over a continuous interval rather than fixed trials, the Poisson distribution fits better. Always confirm the four conditions before trusting binomial results.