BINOM.INV Function

Basic Description

The Excel BINOM.INV function returns the inverse of the Cumulative Binomial Distribution. I.e. for a given number of independent trials, the function returns the smallest value (number of successes) for which the cumulative binomial distribution is greater than or equal to a given probability. For example, the Binom.Inv function could be used to calculate the minimum number of tosses of a coin, for which there is a 50% chance of at least 20 heads. The Binom.Inv function is new in Excel 2010, and so is not available in earlier versions of Excel. However, this function is simply a new version of the Critbinom function, that is available in earlier versions of Excel.

Syntax: BINOM.INV( trials, probability_s, alpha )

where the arguments are listed in the table below :

trials The number of independent trials to be done

(If supplied as a decimal, this value is truncated to an integer by Excel)

probability_s The probability of success in a single trial
alpha The probability of the Cumulative Binomial Distribution (must be between 0 and 1)

 

Binomial Cumulative Distribution Function Chart

Cumulative Distribution Function for the Toss of a Coin in 100 Trials

 

Binom.Inv Function Example

The chart on the right shows the Cumulative Binomial Distribution Function, representing the probability that at most x heads will be thrown from 100 tosses of a coin. The Excel Binom.Inv function calculates the inverse of this function. I.e. the function calculates the minimum value of x for which the Cumulative Binomial Distribution function is a given probability. This is shown in the spreadsheets below, for three different probability values. The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results.

 Formulas:

Examples of use of the Excel Binom.Inv Function

 Results:

Excel Binom.Inv Function Results

 

Trouble Shooting

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

#NUM! Occurs if either the supplied trials argument is < 0 or the supplied probability_s argument is < 0 or > 1 or the supplied alpha argument is < 0 or > 1
#VALUE! Occurs if any of the supplied arguments are nonnumeric

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment