The Excel BINOM.DIST.RANGE Function

Related Function:

Function Description

The Excel Binom.Dist.Range function returns the Binomial Distribution probability for the number of successes from a specified number of trials falling into a specified range.

Note: The Binom.Dist.Range function is new in Excel 2013 and so is not available in earlier versions of Excel.

The syntax of the function is:

BINOM.DIST.RANGE( trials, probability_s, number_s, [number_s2] )

where the arguments are listed in the table below:

trials	-	The number of independent trials that are to be done.
probability_s	-	The probability of success in one trial (must be ≥ 0 and ≤ 1).
number_s	-	The minimum number of successes that you want to calculate the probability for (must be ≤ trials).
[number_s2]	-	An optional argument, defining the maximum number of successes that you want to calculate the probability for. If provided, [number_s2] must be ≥ number_s and ≤ trials. If the [number_s2] argument is omitted, the function calculates the probability of exactly number_s successes.

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

Binom.Dist.Range Function Examples

Binomial Distribution Probability Mass Function Chart

Probability Mass Function for the Toss of a Coin in 100 Trials

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

In the spreadsheet below, the Excel Binom.Dist.Range function is used to evaluate this function for four different ranges of x.

The formulas are shown in the top spreadsheet and the results are shown in the lower spreadsheet.

Formulas:

	A
1	=BINOM.DIST.RANGE( 100, 0.5, 0, 40 )
2	=BINOM.DIST.RANGE( 100, 0.5, 45, 55 )
3	=BINOM.DIST.RANGE( 100, 0.5, 50, 100 )
4	=BINOM.DIST.RANGE( 100, 0.5, 50 )

Results:

	A	B
1	0.028443967	- probability of between 0 & 40 successes
2	0.728746976	- probability of between 45 & 55 successes
3	0.539794619	- probability of between 50 & 100 successes
4	0.079589237	- probability of exactly 50 successes

Note that, in the above examples:

As the probability of tossing a head on any one trial is 0.5, this is input as the value of the probability_s argument in each case.
In cell A4, the [number_s2] argument is omitted and so the function calculates the probability of exactly number_s successes.

For further examples of the Excel Binom.Dist.Range function, see the Microsoft Office website.

Binom.Dist.Range Function Errors

If you get an error from the Excel Binom.Dist.Range Function, this is likely to be one of the following:

Common Errors

#NUM!

Occurs if either:

The supplied probability_s is < 0 or > 1;
The supplied number_s is < 0 or > trials;
The supplied [number_s2] is < 0 or > trials or < number_s;

#VALUE!

Occurs if any of the supplied arguments are non-numeric.

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