Excel Functions Related to Binomial Distributions

BINOM.DIST($x,n,p,FALSE$)

The binomial probability of seeing $x$ successes in $n$ trials where each independent trial has probability $p$ of success. The last argument indicates that the probability returned should not be cumulative.

BINOM.DIST($x,n,p,TRUE$)

The binomial probability of seeing $x$ or fewer successes in $n$ trials where each independent trial has probability $p$ of success. The last argument indicates that the probability is cumulative. That is to say, it gives the sum $P(0) + P(1) + \cdots + P(x)$.

BINOM.INV($n,p,\alpha$)

Returns the smallest value for which the cumulative binomial distribution for $n$ independent trials, each with probability of success $p$, is greater than or equal to $\alpha$.

One can simulate binomial data using the BINOM.INV() function, as seen below.



The relevant formulas to replicate the above are $$\begin{array}{|c|l|}\hline \textrm{cell(s)} & \textrm{formula}\\\hline B3 & =RAND()\\\hline C3 & =BINOM.INV(10,0.5,B3)\\\hline F3 & =COUNTIF($C$3:$C$302,"="\&E3)\\\hline \end{array}$$ The random numbers and associated inverse binomial calculations extend to row 302 (so that there are 300 generated values).

The graph is given by creating a chart (select F3:F13, then select "CHARTS" → "COLUMN" → "CLUSTERED COLUMN") of the data in the range F3:F13. To eliminate gaps between the bars (which is the default), right-click on one of the bars and choose "Format Data Series...". Then, click "Options" and adjust "Gap width" to 0%. The fill color and line thickness can similarly be adjusted from their defaults by clicking on the "Fill" and "Line" sections of the "Options" dialog box.