BINOMDIST Excel (BINOM.DIST)

Excel for Statistics >

The Excel BINOM.DIST / BINOMDIST function is a way to create a binomial distribution from a set of data entered into a worksheet. Use this function to calculate probabilities for problems with:

  • A fixed number of trials,
  • Two outcomes: success or failure,
  • Independent trials,
  • Constant probability of success.

Watch the video for an example:

BINOMDIST Excel Example

Let’s say you’re playing a game of marbles where you have to guess whether your opponent has odd or even marbles (like the game played by Gi-Hun and the old man in Squid Game). As you can’t see your opponents hand (and you’re not a mind reader), you have 50% chance (that’s .5 as a decimal) of guessing right for each round. You play 12 rounds. Here’s how you would set up the worksheet for that information:
EXCEL BINOMDIST SETUP

I’m using Excel 365 for this example, but the function should work in all older versions back to Excel 2010. There is a subtle difference in the format:

  • Excel 2010 & Up: Use BINOM.DIST
  • Excel 2007 and earlier: Use BINOMDIST

BINOMDIST is compatible with all versions, although BINOMDIST may suffer from inaccurate calculations [1]. Microsoft updated the function in 2010 to offer better accuracy.

Using the Excel BINOMDIST Function for Probabilities

Step 1: Click on cell B2. Type =BINOM and double click the BINOM.DIST function.
Using the Excel BINOMDIST Function for Probabilities image

Step 2: Complete the formula (without the quotation marks):
“=BINOM.DIST(A2,D$1,C$1,FALSE)”
The dollar sign next to D1 and C1 will keep that cell choice for all of the remaining calculations. For discrete probabilities (i.e. the probability of getting exactly 8 correct), choose FALSE. Typing TRUE here will give you cumulative probabilities.
excel =BINOM.DIST(A2,D$1,C$1,FALSE) forumla

Step 2: Move the Formula Down the column by clicking on the little blue box at the bottom right of the cell and dragging it to the bottom of the column:
BINOMDIST Excel (BINOM.DIST) - Move the Formula Down the column by clicking on the little blue box at the bottom right of the cell and dragging it to the bottom of the column

An optional step is to create a scatter plot with the data. Highlight the two columns, click the Insert tab and choose Scatter in the Charts section. You can see from the graph that the peak is at x = 6, which means we should expect to get six guesses right. The probability of getting zero or 12 guesses correct is extremely small (so small to be practically impossible):
BINOMDIST Excel (BINOM.DIST) - scatter plot graph

References

[1] Knüsel, L. On the Accuracy of Statistical Distributions in Microsoft Excel 97. Retrieved October 4, 2021 from: https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.368.2820&rep=rep1&type=pdf


Comments? Need to post a correction? Please Contact Us.

Join us on our YouTube Channel