The Excel BINOMDIST Function

BINOMDIST and BINOM.DIST

In Excel 2010, the Binomdist function has been replaced by the Binom.Dist function, which has improved accuracy.

Although it has been replaced, the Binomdist function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

However, the Binomdist function may not be available in future versions of Excel, so it is advised that you use the Binom.Dist function if possible.

Related Functions:

CRITBINOM
NEGBINOMDIST

Function Description

The Excel BINOMDIST function returns the Binomial Distribution probability of a specified number of successes out of a specified number of trials.

The syntax of the function is:

BINOMDIST( number_s, trials, probability_s, cumulative )

where the arguments are as follows:

number_s

The number of successes that you want to calculate the probability for.

trials

The number of independent trials that are to be done.

probability_s

The probability of success in one trial.

cumulative

A logical argument that specifies whether you want to calculate the cumulative distribution function or the probability mass function. This argument can have the value TRUE or FALSE, meaning:

TRUE	-	use the cumulative distribution function;
FALSE	-	use the probability mass function.

Note that if decimal values are input for the number_s or trials arguments, these are truncated to integers by Excel.

+ Display Binomial Distribution Summary:

Binomial Distribution

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

Further information can be found on the Wikipedia Binomial Distribution page

Binomdist Function Examples

Example 1 - Binomial Distribution Probability Mass Function

Binomial Distribution Probability Mass Function Chart

Probability Mass Function for 100 Tosses of a Coin

The above chart on the right shows the Binomial Distribution Probability Mass Function for 100 tosses of a coin. The chart shows the probability that exactly x heads will be thrown from 100 tosses of a coin.

In the spreadsheets below, the Excel Binomdist function is used to evaluate this function for three different values of x.

Clearly, the probability of tossing a head on any one trial is 0.5, so this is input as the probability argument to the functions.

Formulas:

	A
1	=BINOMDIST( 10, 100, 0.5, FALSE )
2	=BINOMDIST( 50, 100, 0.5, FALSE )
3	=BINOMDIST( 65, 100, 0.5, FALSE )

Results:

	A	B
1	1.36554E-17	- probability of exactly 10 heads
2	0.079589237	- probability of exactly 50 heads
3	0.000863856	- probability of exactly 65 heads

Example 2 - Binomial Cumulative Distribution Function

Binomial Cumulative Distribution Function Chart

Cumulative Distribution Function for 100 Tosses of a Coin

The above chart on the right shows the Binomial Cumulative Distribution Function for 100 tosses of a coin. The chart shows the probability that at most x heads will be thrown from 100 tosses of a coin.

In the spreadsheets below, the Excel Binomdist function is used to evaluate the Cumulative Distribution for three different values of x.

Once again, the probability of tossing a head on any one trial is 0.5, so this is input as the probability_s argument.

Formulas:

	A
1	=BINOMDIST( 10, 100, 0.5, TRUE )
2	=BINOMDIST( 50, 100, 0.5, TRUE )
3	=BINOMDIST( 65, 100, 0.5, TRUE )

Results:

	A	B
1	1.53165E-17	- probability of at most 10 heads
2	0.539794619	- probability of at most 50 heads
3	0.999105035	- probability of at most 65 heads

For further examples of the Excel Binomdist function, see the Microsoft Office website.

Binomdist Function Errors

If you get an error from the Excel Binomdist Function, this is likely to be one of the following:

Common Errors

#NUM!

Occurs if either:

The supplied value of the number_s argument is < 0 or > trials;
The supplied probability_s is < 0 or > 1.

#VALUE!

Occurs if any of the number_s, trials, or probability_s arguments are non-numeric.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions