We can use BINOM.Dist function to calculate binomial distribution probably for a range of successes from a number of trials. The Binomial Distribution is a statistical measure used in displaying the probability of a specific number of successes from a particular number of independent trials. For example, we can use the Excel binomial distribution to calculate for the number of heads we can get in 100-coin tosses.

Figure 1 – Example of binomial distribution in Excel

Binomial Distribution Formula

The syntax of the BINOM.DIST function is:

=BINOMDIST(number_s, trials, probability_s, cumulative)

Where

  • Numbers_s – the number of successes we wish to calculate a probability
  • Trials – the number of independent trials
  • Probability_s  the probability of success in one trial
  • Cumulative – the logical argument that specifies if we wish to calculate for the probability mass function or cumulative distribution function. This argument can have:
          + TRUE for cumulative distribution function and
          + FALSE for probability mass function.

How to do Binomial distribution

We can either calculate for the binomial distribution using cumulative distribution function or probability mass function. In this tutorial, we will do both using a single hypothetical data set.

We can set up a hypothetical data table as shown below:

Figure 2 – Setting up data for Excel Binomial distribution

   1. We will calculate for Binomial distribution using cumulative distribution function

          a. We will enter the description in Cell B10 and insert the formula below in Cell C10.

=BINOM.DIST(C4,C5,C6,TRUE)

Figure 3 – How to Use Binomial Distribution

  • We will get the result below

Figure 4 – Binomial distribution using Cumulative Distribution Function

   2. Now, we will calculate the binomial distribution using probability mass function

          b. We will enter the description in Cell B11 and insert the formula below in Cell C11

=BINOM.DIST(C4,C5,C6,FALSE)

Figure 5 – Using the BINOM.DIST function

  • We will get the result below

Figure 6 – Using Probability Mass Function for Binomial distribution

Note

We can get one of these errors when carrying out the Excel Binomial function

  • #VALUE – this happens when the number of probability argument or trials is non-numeric.
  • The BINOM.DIST is different from the BINOMDIST and can be found in Excel 2010 and higher. We use BINOM.DIST function because it has improved accuracy.