The BINOM.DIST function returns the binomial distribution probability for a given number of successes from a specified number of trials.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • BINOM.DIST replaces the BINOMDIST function included in earlier versions of Excel.

Syntax

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

Arguments

Argument Description
number_s The number of successes in trials
trials The number of independent trials
probability_s The probability of success on each trial
cumulative A logical argument that specifies the type of binomial distribution to be calculated. This can have the value TRUE or FALSE meaning:

  TRUE use the cumulative distribution function
  FALSE use the probability mass function

Examples

  A B C D
1 Data Formula    
2 6 Number of successes in trial    
3 10 Number of independent trials    
4 0.5 Probability of success on each trial    
5        
6 Formula Result Notes
7 =BINOM.DIST(A2,A3,A4,FALSE) 0.2050781 Probability of exactly 6 of 10 trials being successful

Usage note: Use BINOM.DIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOM.DIST can calculate the probability that two of the next three babies born are male.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the number_strials, or probability_s arguments are nonnumeric
#NUM! Occurs if either:

  the supplied number_s is < 0 or > trials
  the supplied probability_s is < 0 or > 1

The Binomial Distribution is a statistical measure that is frequently used to indicate the probability of a specific number of successes occurring from a specific number of independent trials.

The following two forms are used:

  • The Probability Mass Function – calculates the probability of there being exactly x successes from n independent trials
  • The Cumulative Distribution Function – calculates the probability of there being at most x successes from n independent trials

The binomial probability mass function is:  

    \[    \begin{aligned} B(x,p,n) = & \binom{n}{x} p^x(1-p)^{n-x} \ \ \ \ \ \ \text{for} \ \ x = 1, 2, 3, \cdots , n \\& \\\text{where: } \binom{n}{x} = & \frac {n!}{x!(n-x)!}  \end{aligned}   \]

The cumulative binomial distribution is:

    \[    F(x,p,n) = \sum_{i=0}^x \binom{n}{i} p^i (1-p)^{n-i}    \]

See Wikipedia for more information on binomial distribution.