How to Use BINOM.DIST in Excel
BINOM.DIST implements the binomial distribution in Excel, answering questions about scenarios with exactly two possible outcomes repeated multiple times. If you're testing 100 products for defects,...
Key Insights
- BINOM.DIST calculates binomial probabilities in Excel using four parameters: number of successes, total trials, probability per trial, and whether you want cumulative or individual probability
- Use FALSE for the cumulative parameter to find the probability of exactly X successes (PMF), or TRUE to find the probability of X or fewer successes (CDF)
- The function excels at modeling scenarios with binary outcomes like pass/fail, yes/no, or success/failure across repeated independent trials
Understanding BINOM.DIST Fundamentals
BINOM.DIST implements the binomial distribution in Excel, answering questions about scenarios with exactly two possible outcomes repeated multiple times. If you’re testing 100 products for defects, running 50 A/B test trials, or analyzing conversion rates across customer touchpoints, this function calculates the probability of getting a specific number of successes.
The binomial distribution applies when you have:
- A fixed number of independent trials
- Each trial has only two outcomes (success/failure)
- The probability of success remains constant across all trials
- You want to know the probability of getting exactly X successes, or at most X successes
Common applications include quality control testing, marketing response analysis, risk assessment modeling, and any scenario where you’re counting binary outcomes across multiple attempts.
Function Syntax Breakdown
The BINOM.DIST function follows this structure:
=BINOM.DIST(number_s, trials, probability_s, cumulative)
Each parameter serves a specific purpose:
number_s: The number of successes you’re analyzing. This must be an integer between 0 and your total number of trials. If you’re asking “what’s the probability of 7 defects,” this value is 7.
trials: The total number of independent trials or attempts. Must be a non-negative integer. For 50 product inspections, this is 50.
probability_s: The probability of success on each individual trial, expressed as a decimal between 0 and 1. A 5% defect rate translates to 0.05.
cumulative: A logical value (TRUE or FALSE) that determines the type of probability returned. FALSE gives you the probability mass function (exactly X successes), while TRUE gives you the cumulative distribution function (X or fewer successes).
Here’s a basic example with explicit values:
=BINOM.DIST(5, 20, 0.3, FALSE)
This calculates the probability of getting exactly 5 successes in 20 trials when each trial has a 30% success probability.
Finding Exact Probability with PMF
When you set the cumulative parameter to FALSE, BINOM.DIST returns the probability of achieving exactly the specified number of successes—no more, no fewer. This is the probability mass function (PMF).
Consider a coin flip scenario. You flip a fair coin 10 times and want to know the probability of getting exactly 7 heads:
=BINOM.DIST(7, 10, 0.5, FALSE)
This returns approximately 0.117 or 11.7%.
Let’s apply this to a business context. Your email marketing team sends campaigns to 100 customers, and historically 15% respond. What’s the probability that exactly 20 customers respond?
=BINOM.DIST(20, 100, 0.15, FALSE)
The result is about 0.0454 or 4.54%—relatively unlikely, since 20 responses is above the expected value of 15.
You can build a probability distribution table by varying the number_s parameter:
| Responses | Probability Formula | Result |
|-----------|----------------------------------|--------|
| 10 | =BINOM.DIST(10, 100, 0.15, FALSE)| 2.37% |
| 15 | =BINOM.DIST(15, 100, 0.15, FALSE)| 9.60% |
| 20 | =BINOM.DIST(20, 100, 0.15, FALSE)| 4.54% |
| 25 | =BINOM.DIST(25, 100, 0.15, FALSE)| 0.58% |
This shows the probability distribution peaks around the expected value and decreases as you move away from it.
Calculating Cumulative Probability with CDF
Setting cumulative to TRUE changes the function’s behavior entirely. Instead of calculating the probability of exactly X successes, it calculates the probability of getting X or fewer successes. This is the cumulative distribution function (CDF).
In quality control, you manufacture batches of 50 components with a historical defect rate of 5%. You need to know the probability that 3 or fewer items in a batch are defective:
=BINOM.DIST(3, 50, 0.05, TRUE)
This returns approximately 0.7604 or 76.04%. There’s a 76% chance you’ll find 3 or fewer defects in any given batch.
The cumulative function is particularly useful for setting acceptance thresholds. If you want to reject batches that fall in the worst 10% of outcomes, you can find that threshold by testing different values:
=BINOM.DIST(5, 50, 0.05, TRUE) // Returns ~0.9622 (96.22%)
=BINOM.DIST(6, 50, 0.05, TRUE) // Returns ~0.9882 (98.82%)
This tells you that finding 5 or fewer defects happens 96% of the time—well above your 90% threshold.
Real-World Business Applications
Marketing Campaign Analysis
You’re running a webinar promotion to 200 qualified leads. Based on past campaigns, 12% of contacted leads register. You want to understand the probability distribution of registrations to set realistic targets.
Set up your Excel sheet with named cells:
// Cell B1 (Leads_Contacted): 200
// Cell B2 (Historical_Rate): 0.12
// Cell B3 (Target_Registrations): 30
// Probability of exactly 30 registrations:
=BINOM.DIST(B3, B1, B2, FALSE)
// Result: ~3.15%
// Probability of 30 or fewer registrations:
=BINOM.DIST(B3, B1, B2, TRUE)
// Result: ~95.37%
// Probability of MORE than 30 registrations:
=1 - BINOM.DIST(B3, B1, B2, TRUE)
// Result: ~4.63%
This analysis reveals that while exactly 30 registrations is unlikely (3.15%), getting 30 or fewer is highly probable (95.37%). If you need more than 30 registrations, you should contact additional leads.
Manufacturing Defect Analysis
A production line creates 1000 units per day with a target defect rate of 2%. You want to establish quality control limits for when to halt production for inspection.
// Expected defects per day: 1000 * 0.02 = 20
// Upper control limit (95th percentile):
// Test values until cumulative probability ≈ 0.95
=BINOM.DIST(27, 1000, 0.02, TRUE) // Returns ~0.9497
// Probability of exceeding this limit:
=1 - BINOM.DIST(27, 1000, 0.02, TRUE) // Returns ~0.0503
You’d set 27 defects as your upper control limit. Exceeding this triggers an investigation, as it only happens 5% of the time under normal conditions.
Sales Conversion Modeling
Your sales team qualifies 50 prospects monthly, with a 25% historical close rate. Leadership asks for the probability of hitting different revenue targets.
// Cell B1 (Prospects): 50
// Cell B2 (Close_Rate): 0.25
// Cell B3 (Deal_Value): $10,000
// Probability of closing exactly 15 deals:
=BINOM.DIST(15, B1, B2, FALSE)
// Result: ~8.37%
// Probability of closing 10 or fewer deals (worst case):
=BINOM.DIST(10, B1, B2, TRUE)
// Result: ~12.11%
// Probability of closing 15 or more deals:
=1 - BINOM.DIST(14, B1, B2, TRUE)
// Result: ~37.51%
With this analysis, you can confidently state there’s a 37.51% chance of closing 15+ deals, which translates to $150,000+ in revenue.
Avoiding Common Mistakes
Parameter Validation: Excel doesn’t always catch invalid inputs gracefully. Wrap your formulas in error checking:
=IF(OR(B2<0, B2>1), "Error: Probability must be 0-1",
BINOM.DIST(B1, B3, B2, FALSE))
Choosing the Right Distribution: BINOM.DIST only works for binary outcomes with constant probability. If your probability changes between trials, you need a different approach. If you’re sampling without replacement from a small population, use HYPGEOM.DIST instead.
Understanding Cumulative vs. Non-Cumulative: The most common error is using TRUE when you want FALSE or vice versa. Remember: FALSE = exactly X, TRUE = X or fewer. If you want “X or more,” use =1 - BINOM.DIST(X-1, trials, probability, TRUE).
Large Number Performance: For trials exceeding 10,000, BINOM.DIST can slow down. Consider using the normal approximation when n×p and n×(1-p) are both greater than 5:
// Normal approximation for large n
Mean = trials * probability_s
StdDev = SQRT(trials * probability_s * (1 - probability_s))
=NORM.DIST(number_s, Mean, StdDev, TRUE)
Related Functions: Excel provides BINOM.INV for finding the inverse (what number of successes corresponds to a given probability) and BINOM.DIST.RANGE for calculating probabilities across a range of successes. Use these when appropriate:
// Find minimum successes for 90% cumulative probability
=BINOM.INV(100, 0.15, 0.9)
// Probability of 10 to 20 successes (inclusive)
=BINOM.DIST.RANGE(100, 0.15, 10, 20)
BINOM.DIST transforms abstract probability theory into actionable business intelligence. Whether you’re setting quality standards, forecasting campaign results, or modeling sales outcomes, this function provides the mathematical foundation for data-driven decision making. Master these techniques, and you’ll have a powerful tool for quantifying uncertainty in any binary-outcome scenario.