In statistics, a binomial distribution is a method of calculating the probability of the number of successes within a set of trials. Binomial also means that there are only ever two possible outcomes – success or failure. Real life examples may include something like whether a university student passes or fails an exam. In this simple guide we are going to run through the process to calculate binomial distribution in Excel.
As with other statistical methods, Excel is a really powerful tool for calculating binomial distribution, and can be done very easily through the use of the built in BINOM.DIST function,
We will run through:
- The types of functions in excel
- Examples with outputs
- Considerations
Let’s get started by running through the formula itself.
BINOM.DIST formula in Excel
As mentioned above, the build in function for calculating binomial distribution makes this nice and easy. The BINOM.DIST function has four values that need to be inputted, which is as follows:
=BINOM.DIST(number_s, trials, probability_s, cumulative)
Let’s break down what each of these means:
number_s – This is the likely hood of X number of successes. If we enter 5 then the output will be the likelyhood of ‘success’ happening 5 times.
trials – This is the number of trials we are running
probability_s – Based on existing data what has the success rate been
cumulative – This is true or false. True means we are measuring as a cumulative, False means probability. Broken out further the two options are:
Cumulative TRUE – Being a cumulative measure, the number output here is the likelyhood of recieving at most the number of successes indicated in the first input
Cumulative FALSE – This swaps us to a probability of there being exactly the number of successes we have entered in the first input
Now that we have each element of the formula broken out, let’s look at a series of examples using both the cumulative true and false options.
Examples of calculating Binomial Distribution
To show off how we can go about calculating binomial distribution in Excel we are going to run with an example that is relatively simple. As you will see in the formula itself, our probability_s is at 0.50 so it is based on an example where the chances of success are at 50%
Going from a small sample size of 0 through to 10, and also matching the number of successes with the number of trials at 10, this gives us a nice clean sample size to work with to show off the potential outputs of both types.
Our inputs and outputs are below, starting with the Cumulative TRUE option:
As we can see, since we are looking at the probability of a ‘max’ number of successes being 10, this essentially means that with a number of tests at 10 as well, and our 50/50 chance of a success we are basically left with a 100% chance of the possibility of a maximum number of successes of 10.
Let’s look at the Cumulative FALSE outputs now:
This one is quite different as we aren’t looking at the possibility of the max number of successes which eventually reaches 100%, but what are the chances of exactly that many successes – and as we would expect once we get up to 10 the number is at its lowest (alongside 0). With the dataset we are working with as well it peaks right in the middle where we have around a 25% chance of 5 successes in 10 trials.
Finally, let’s take a quick look at some callouts we need to be aware of with the BINOM.DIST function.
Considerations
One issue that people may come across when working with this formula is a #NUM! error. This can be pretty common when we are trying to work with datasets that aren’t technically possible.
An example of this would be if we went further than 10 in the successes column, but kept our number of tests at 10. It is impossible to have 11 successes in a set of 10 trials!
Similar things happen when we work with negatives – we can’t have less than 0 successes so that will result in the same error.
This sums up our guide on how to calculate binomial distribution in Excel.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.