How to do Binomial Distribution in Excel

Excel has got many features connected with statistics. For examples Excel could help you to calculate binomial distribution (aka bernoulli distribution).

Let’s learn how to do it in this easy steps.

What is Binomial Distribution?

The binomial distribution is useful for describing a binomial (“zero-one”) process, for example, the number of women and men in a random sample from several companies or the number of defective items in a sample of 20 taken in a manufacturing process. The definition function is defined as: f(x) = [n!/ (x! * (n-x)!)] * px * qn-x, for x = 0,1,2, …, n

Business example of Binomial Distribution

Here’s the real business example how you can use the binomial distibution in Excel.

The production of a your company products includes 35% of the 1st grade products, the rest are 2nd grade products. The customer purchased 10,000 items of products. Using binomial distribution calculate:

  • the probability that only 3000 of the purchased products will be 1st grade
  • the probability that only 3000 of the purchased products will be 2nd grade
  • what average number of 1st grade products can the customer expect if he purchases 50,000 items

Binomial Distribution in Excel

This is the basic binomial distribution example. To do that first enter data in Excel sheet and form three columns, one indicating no. of successes, probability of success and trials.

binomial distribution data set table

2. It is calculated by using the BINOM.DIST formula.

BINIM.DIST formula

BINOM.DIST formula used in this binomial coefficient distribution example:

See also  How to calculate t value in Excel

=BINOM.DIST(A5,B5,C5,0)

binomial distribution probability

And this is the result of binomial distribution Excel calculations.