Statistics How To

BINOMDIST Excel (BINOM.DIST)

Share on

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:


Can’t see the video? Click here.

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.


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.

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:


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):

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

CITE THIS AS:
Stephanie Glen. "BINOMDIST Excel (BINOM.DIST)" From StatisticsHowTo.com: Elementary Statistics for the rest of us! https://www.statisticshowto.com/binomdist-excel-binom-dist/
---------------------------------------------------------------------------

Need help with a homework or test question? With Chegg Study, you can get step-by-step solutions to your questions from an expert in the field. Your first 30 minutes with a Chegg tutor is free!

Need help with a homework or test question? With Chegg Study, you can get step-by-step solutions to your questions from an expert in the field. Your first 30 minutes with a Chegg tutor is free!

Comments? Need to post a correction? Please post a comment on our Facebook page.