How to use BINOM.DIST Function in Excel

The Excel BINOM.DIST function returns the Binomial Distribution probability for a given number of successes from a specified number of trials.

The function is new in Excel 2010 and so is not available in earlier versions of Excel. However, the Binom.Dist function is simply an updated version of the Binomdist function that is available in earlier versions of Excel.

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

The BINOM.DIST function syntax has the following arguments:

• Number_s     Required. The number of successes in trials.
• Trials     Required. The number of independent trials.
• Probability_s     Required. The probability of success on each trial.

• Cumulative     Required. A logical value that determines the form of the function. If cumulative is TRUE, then BINOM.DIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if FALSE, it returns the probability mass function, which is the probability that there are number_s successes.

Example: Let’s look at some Excel BINOM.DIST function examples and explore how to use the BINOM.DIST function as a worksheet function in Microsoft Excel:

Example 1: Example of BINOM.DIST Function in Excel which calculates probability mass function:

• In the above example success is defined as number of heads on toss of a coin.
• In the above example Binomial Distribution Function – BINOM.DIST takes up the number of success which ranges from 100 to 900, number of trials which is 100, probability of success in each trial which is 0.5, and cumulative as FALSE, so that probability mass function is returned.

So the result will be a probability mass function

Syntax:  =BINOM.DIST(B1,B10,B11,FALSE)

Result: 5.9589E-162

Based on the Excel spreadsheet above, the following BINOM.DIST examples would return:

When we plot the result with number of success on x axis we will get the graph for probability mass function which is shown below.

Example 2: Example of BINOM.DIST Function in Excel which calculates Cumulative Distribution Function:

• In the above example success is defined as number of heads on toss of a coin.
• In the above example Binomial Distribution Function – BINOM.DIST takes up the number of success which ranges from 100 to 900, number of trials which is 100, probability of success in each trial which is 0.5, and cumulative as TRUE, so that cumulative distribution function is returned.

So the result will be a cumulative distribution function

Syntax: =BINOM.DIST(B1,B10,B11,TRUE)

Result: 6.7017E-162

Based on the Excel spreadsheet above, the following BINOM.DIST examples would return:

When we plot the result with number of success on x axis we will get the graph for cumulative distribution function which is shown below

Note:

1. The BINOM.DIST function will truncate all numerical values to Integer.
2. #VALUE! error – Occurs when any of the arguments provided is non-numeric.
3. #NUM! error – Occurs when:
   1. The given probability is less than zero or greater than 1.
   2. The given number_s is less than zero or greater than the number of trials.
4. The binomial probability mass function is:
   
   where:
   
   is COMBIN(n,x).
   The cumulative binomial distribution is: