BINOM.DIST Function in Google Sheets: Complete Guide

Key Insights

• BINOM.DIST calculates the probability of achieving a specific number of successes in a fixed number of independent trials, making it essential for quality control, A/B testing, and any yes/no outcome analysis.
• The cumulative parameter (TRUE/FALSE) fundamentally changes what you’re calculating—use FALSE for “exactly X successes” and TRUE for “X or fewer successes.”
• Combine BINOM.DIST with SEQUENCE to generate entire probability distributions in a single formula, enabling quick visualization and analysis without manual data entry.

Introduction to Binomial Distribution

Binomial distribution answers a straightforward question: given a fixed number of independent trials where each trial has only two outcomes (success or failure), what’s the probability of getting exactly X successes?

Three conditions must hold for binomial distribution to apply:

1. Fixed number of trials — You know how many attempts you’re making
2. Independent trials — Each outcome doesn’t affect the others
3. Constant probability — The success rate stays the same across all trials

Real-world applications are everywhere. Quality control engineers use it to predict defect rates in manufacturing batches. Marketing teams calculate expected response rates for campaigns. Educators estimate how many students will pass standardized tests. Product managers analyze feature adoption rates.

Google Sheets’ BINOM.DIST function handles all the complex probability math, letting you focus on the business problem rather than factorial calculations.

BINOM.DIST Syntax and Parameters

The function signature is:

BINOM.DIST(num_successes, num_trials, prob_success, cumulative)

Here’s what each parameter means:

Let’s start with the classic coin flip example. What’s the probability of getting exactly 3 heads in 5 coin flips?

=BINOM.DIST(3, 5, 0.5, FALSE)

This returns 0.3125 or 31.25%. The parameters break down as:

• 3 successes (heads)
• 5 total flips
• 0.5 probability of heads on each flip
• FALSE because we want exactly 3, not “3 or fewer”

Cumulative vs. Non-Cumulative (PDF vs. CDF)

The cumulative parameter is where most people get confused. It fundamentally changes what the function calculates.

FALSE (Probability Mass Function / PDF): Returns the probability of getting exactly the specified number of successes. Use this when you need precise counts.

TRUE (Cumulative Distribution Function / CDF): Returns the probability of getting at most the specified number of successes (i.e., that number or fewer). Use this for “up to” questions.

Here’s a side-by-side comparison using 10 trials with a 30% success rate:

// Probability of EXACTLY 3 successes
=BINOM.DIST(3, 10, 0.3, FALSE)
// Result: 0.2668 (26.68%)

// Probability of 3 OR FEWER successes
=BINOM.DIST(3, 10, 0.3, TRUE)
// Result: 0.6496 (64.96%)

The cumulative version sums up P(0) + P(1) + P(2) + P(3).

Need “more than X” instead of “at most X”? Subtract from 1:

// Probability of MORE THAN 3 successes (i.e., 4 or more)
=1 - BINOM.DIST(3, 10, 0.3, TRUE)
// Result: 0.3504 (35.04%)

Need “at least X”? Subtract one less from 1:

// Probability of AT LEAST 3 successes (i.e., 3 or more)
=1 - BINOM.DIST(2, 10, 0.3, TRUE)
// Result: 0.6172 (61.72%)

Practical Examples and Use Cases

Manufacturing Defect Analysis

A factory produces circuit boards with a 2% defect rate. In a batch of 50 boards, what’s the probability of finding exactly 2 defective units?

=BINOM.DIST(2, 50, 0.02, FALSE)
// Result: 0.1858 (18.58%)

More useful for quality control: what’s the probability of finding 3 or more defects (triggering a batch review)?

=1 - BINOM.DIST(2, 50, 0.02, TRUE)
// Result: 0.0784 (7.84%)

This tells you roughly 8% of batches will need additional inspection.

Marketing Campaign Response Rates

Your email campaign historically gets a 5% click-through rate. You’re sending to 200 recipients. What’s the probability of getting at least 15 clicks?

=1 - BINOM.DIST(14, 200, 0.05, TRUE)
// Result: 0.0673 (6.73%)

Only about 7% chance of hitting 15+ clicks. If your goal requires 15 clicks, you might need a larger send list or improved targeting.

Exam Pass Probability

A certification exam has 80 questions. A candidate needs 70% (56 correct) to pass. If they have an 80% chance of answering each question correctly, what’s their probability of passing?

=1 - BINOM.DIST(55, 80, 0.8, TRUE)
// Result: 0.9970 (99.70%)

Strong odds. But what if they’re only 65% confident on each question?

=1 - BINOM.DIST(55, 80, 0.65, TRUE)
// Result: 0.1867 (18.67%)

The pass probability drops dramatically—a useful insight for test preparation.

A/B Testing Sample Size Validation

You’re running an A/B test where the control converts at 10%. You want to detect if variant B achieves 12% conversion. With 500 users per variant, what’s the probability the control group shows 60+ conversions (which might mask the improvement)?

=1 - BINOM.DIST(59, 500, 0.10, TRUE)
// Result: 0.0668 (6.68%)

About 7% chance of a false signal from the control group alone—useful for understanding test reliability.

Visualizing Binomial Distributions

Static numbers are hard to interpret. Building a probability distribution chart makes patterns obvious.

Use SEQUENCE combined with BINOM.DIST to generate an entire distribution in one formula. For 20 trials with 40% success probability:

// In cell A1, generate success counts 0-20
=SEQUENCE(21, 1, 0, 1)

// In cell B1, calculate probabilities for each count
=ARRAYFORMULA(BINOM.DIST(A1:A21, 20, 0.4, FALSE))

Or combine into a single two-column array:

=ARRAYFORMULA({
  SEQUENCE(21, 1, 0, 1),
  BINOM.DIST(SEQUENCE(21, 1, 0, 1), 20, 0.4, FALSE)
})

This outputs:

Select both columns and insert a column chart. You’ll see the classic bell-shaped binomial distribution, peaked around 8 (which is 20 × 0.4, the expected value).

For cumulative visualization, change FALSE to TRUE:

=ARRAYFORMULA(BINOM.DIST(SEQUENCE(21, 1, 0, 1), 20, 0.4, TRUE))

This produces the S-curve characteristic of cumulative distributions.

Common Errors and Troubleshooting

#NUM! Error

This occurs when parameters fall outside valid ranges:

// num_successes greater than num_trials
=BINOM.DIST(15, 10, 0.5, FALSE)
// Result: #NUM!

// Negative values
=BINOM.DIST(-1, 10, 0.5, FALSE)
// Result: #NUM!

// Probability outside 0-1 range
=BINOM.DIST(5, 10, 1.5, FALSE)
// Result: #NUM!

Fix: Validate inputs before calculation:

=IF(AND(A1>=0, A1<=B1, C1>=0, C1<=1), 
    BINOM.DIST(A1, B1, C1, FALSE), 
    "Invalid parameters")

#VALUE! Error

Happens with non-numeric inputs:

=BINOM.DIST("five", 10, 0.5, FALSE)
// Result: #VALUE!

Fix: Use ISNUMBER checks or VALUE conversion for text-formatted numbers.

Decimal Successes and Trials

Google Sheets truncates decimals to integers:

=BINOM.DIST(3.7, 10.9, 0.5, FALSE)
// Treated as BINOM.DIST(3, 10, 0.5, FALSE)

This is usually fine, but be aware if you’re passing calculated values.

Related Functions

BINOM.DIST.RANGE: Calculates probability of successes falling within a range:

// Probability of 3 to 7 successes in 10 trials at 50%
=BINOM.DIST.RANGE(10, 0.5, 3, 7)
// Equivalent to: BINOM.DIST(7,10,0.5,TRUE) - BINOM.DIST(2,10,0.5,TRUE)

BINOM.INV: The inverse function—returns the smallest success count for a given cumulative probability:

// What's the minimum successes to be in the top 10%?
=BINOM.INV(20, 0.5, 0.9)
// Result: 13

Summary and Quick Reference

BINOM.DIST is your go-to function for any scenario involving repeated yes/no trials with known probabilities. The key decision point is the cumulative parameter: FALSE for exact counts, TRUE for “at most” scenarios.

Remember the constraints: successes must be integers between 0 and trials, and probability must be between 0 and 1. When building models, wrap your formulas in validation logic to catch edge cases before they produce cryptic errors.