The BINOM.DIST function in Excel is an essential Statistical Function that came into existence in 2010. The function is easy to understand in this first go.
So let us begin learning.
When to Use BINOM.DIST Function in Excel?
The word BINOM.DIST represents binomial distribution. The BINOM.DIST Function in Excel is used to get the probability of a number of successes when the same experiment is conducted several times. The BINOM.DIST Function has further two kinds of results:-
- Probability Distribution – this gets us the probability of exactly x number of successes in n number of trials,
- Cumulative Distribution – we would get the probability of having at most x number of successes in n number of trials
What does the Result of BINOM.DIST Function Implies Calculates?
This can be understood with a simple example. Let us suppose tossing a coin is an experiment. Getting a head on the top of each coin is a success. We would repeat this experiment of tossing a coin 3 times. So the total number of trials n=3.
The following can be the possibilities when a coin is tossed three times:-
Coin 1 | Coin 2 | Coin 3 | Number of Heads |
Head | Head | Head | 3 |
Head | Head | Tail | 2 |
Head | Tail | Head | 2 |
Tail | Head | Head | 2 |
Head | Tail | Tail | 1 |
Tail | Head | Tail | 1 |
Tail | Tail | Head | 1 |
Tail | Tail | Tail | 0 |
There can be 8 possible outcomes when a coin is tossed 3 times
Probability Distribution
Let us suppose that getting a head on the coin is a success. The probability of getting a head on a coin is 1/2 and not getting a head is also 1/2 (tail on top). Therefore the probability of success is 1/2.
We want to get the probability of getting 2 heads when a coin is tossed three times. Here are three cases when we get two heads after tossing three coins.
(HHT,HTH,THH)
So the probability of getting exactly two heads when a coin is tossed three times would be 3/8
This is when we use the probability function of getting x number of successes when an experiment is conducted n times
Cumulative Distribution
There can be another situation when we want the probability of almost 2 heads ( 0 heads + 1 head + 2 heads )coming when the coin is tossed 3 times. The following would be favorable outcomes.
(TTT,HTT,THT,TTH,HHT,HTH,THH)
Therefore the probability of getting at most 2 heads when a coin is tossed 3 times in 7/8
This is when we use Cumulative Distribution to get the probability of at most x successes when the experiment is conducted n times.
Also Read: BINOM.DIST.RANGE Function in Excel
Syntax and Examples
=BINOM.DIST(number_s,trials,probability_s,cumulative)
The following points contain important notes about BINOM.DIST Function in Excel.
- number_s – This function asks for the number of successes out of n trials. It cannot be a negative value or a value greater than the actual number of trials performed ( you can’t get 4 heads when a coin is tossed three times )
- trials – This is the number of times, the experiment is repeated ( rolling a dice 5 times, picking a card 10 times )
- probability_s – This is the probability of success in an experiment ( if getting 6 on dice is a success then the probability of getting a 6 when a die is rolled is 1/6 )
- cumulative – This argument is boolean and asks for the type of distribution. It can have two values.
- FALSE – We want to get the probability of exactly x successes.
- TRUE – We want the probability of at most x successes.
Examples to learn BINOM.DIST Function in Excel
In this section of the blog, we would be performing some experimentation with the BINOM.DIST Function.
Example 1 – Getting x Heads on Coin Tossed N Times
Let us suppose you have a coin and you tossed it once. There can be a head or a tail when on top of a coin upon tossing it. If Getting a Head on the coin is a success then the probability of success would be 1/2 or 0.50 or 50%.
If the same coin is tossed five times, then we want to find the probability of getting the head 2 times. Use the following BINOM.DIST function formula.
=BINOM.DIST(2,5,0.5,FALSE)
As a result, we get the probability of getting 2 heads when the coin is tossed 5 times to be 0.31 or 31%.
Explanation – The number of trials is 5 and the successes for which we want to find the probability is 3. So the number_s is 3 and trials argument is 5. Thereafter, the probability_s of getting success (head ) is 1/2 as each time the experiment is conducted ( coin is tossed ). At last, we set the cumulative argument as FALSE, as we want the result for exactly 2 heads.
If you want to get the probability of getting at most two heads, then you can keep the formula the same, just change the cumulative argument to TRUE.
This implies that the probability of getting at most 2 heads ( zero head or 1 head or 2 heads ) when the coin is tossed five times is 0.50 or 50%.
Example 2 – Success in Throwing a Dice
Let us suppose a man has a dice. He assumes that getting a 1 or 6 on the dice is a success when he rolls the dice. The probability of getting success is 2/6.
The man rolls the dice 10 times. We want to know his chances of getting success 7 times ( getting a 1 or 6 seven times ).
We would use the following BINOM.DIST Function Formula.
=BINOM.DIST(7,10,2/6,FALSE)
As a result, we know that his probability of getting a 1 or 6, seven times would be 0.02 or 2 %.
Explanation – We supplied the number_s and trials argument as 7 and 10 respectively. Success is defined by getting a 1 or 6 on the dice. Therefore, the probability_s argument is 2/6.
Similarly, we can find the chances of the man getting a one or six on dice at most 7 times, only by changing the cumulative argument to TRUE
This implies. that the chances of getting a one or six, more than 7 times is only 0.003 or 0.3 %
You can use the BINOM.DIST.RANGE Function for the number of successes lying in a range.
This brings us to the end of the blog.
Thank you for reading.
RELATED POSTS
- CHISQ.DIST Function in Excel – Chi-Square Distribution
- BETA.DIST Function in Excel – A Statistical Function
- NORM.DIST Function in Excel – Get PDF and CDF
- CHISQ.DIST.RT Function in Excel – Statistical Formula
- CHISQ.INV Function in Excel – Inverse Chi Square Distribution
- VAR.P Function in Excel – A Statistical Function