In the previous article, we covered the usage of BINOM.DIST Function. BINOM.DIST.RANGE function in excel is an extension of the existing BINOM.DIST Function. It is highly recommended to learn the simple BINOM.DIST Function to completely understand the BINOM.DIST.RANGE Function.
So let us get started.
When to Use BINOM.DIST.RANGE Function in Excel?
The BINOM.DIST.RANGE Function finds the Probability of happening of a number of successes out of several trials, lying in a range. If we perform N trials of an experiment, then the range of the number of successes can lie in the interval [0, N].
The BINOM.DIST.RANGE is an important type of Statistical Function. It uses the concept of Binomial Distribution as explained in the next section.
Concept of Binomial Distribution in Range
The BINOM.DIST.RANGE Function in Excel uses the following formula.
When an experiment is repeated n times and we want to get the probability of getting r successes out of n trials, then this formula is used.
Here:-
- n denotes the total number of times the experiment is conducted.
- r denotes the number of successes for which we want to get the probability
- p denotes the probability of success in each experiment. p is raised to power r.
- n-r denotes the number of failures.
- q denotes the probability of failure in each experiment. q = 1 – p. q is raised to power n-r ( total failures )
BINOM.DSIT Function has the ability to get the probability of getting exactly r successes when the n trials are performed. For example, if getting a head upon tossing a coin is considered a success, then BINOM.DIST can find the probability of getting exactly 3 heads ( r=3 ) when the same coin is tossed 5 times ( n=5 )
On the other hand, BINOM.DIST.RANGE Function calculates the probability of successes lying in a range out of n trials. If a coin is tossed 5 times ( n=5 ) then BINOM.DIST Function can find the probability of getting heads from 1 to 3. The result would count the probability of getting 1 head, 2 heads, or 3 heads when the coin is tossed 5 times.
Where 1/2 is the probability of success when getting a head on coin and 1 – 1/2 is for failure ( getting a tail ).
All of these manual calculations can be avoided with the use of BINOM.DIST.RANGE Function of Excel
Syntax and Arguments
=BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2])
The following points describe the inputs required by BINOM.DIST.RANGE Function of Excel.
- trials – This asks for the total number of times, the experiment is repeated.
- probability_s – Here we supply the probability of success in each experiment.
- number_s – This asks for the number of successes for which we want to get the value of probability. It must be a value like 0 or greater than 0 but cannot be greater than a total number of trials.
- [number_s2] – This is an optional argument, when we supply it, the BINOM.DIST.RANGE Function finds the probability of getting successes falling in the range number_s and number_s2, number_s2 must be greater than number_s but it cannot be greater than trials.
Examples to Use BINOM.DIST.RANGE Function
In this section of the blog, we would now implement the BINOM.DIST.RANGE Function.
Example 1 – Finding Probability of Getting exactly 2 Heads
Let us suppose a person has a coin and he has tossed it 3 times. We want to find his probability of getting exactly two heads. Use the following BINOM.DIST.RANGE formula.
=BINOM.DIST.RANGE(3,0.5,2)
As a result, we got the probability of getting exactly 2 heads as 0.38 or 38%.
Explanation – We have supplied the trials argument as 3 because the person is tossing the coin three times. Moreover, getting a head on the coin is a success. The probability of getting a head upon tossing a coin is o.5 or 50%. The probability_s becomes 0.5. We wanted to find the probability of getting exactly 2 heads, Therefore, the number_s argument becomes 2. We did not specify the number_s2 argument as this is not a case for range.
Note that the same result can be concluded with the following BINOM.DIST Function Formula.
=BINOM.DIST(2,3,0.5,0)
Example 2 – Finding Probability of Successes lying in a Range
Let us suppose you have dice. If getting a six on the dice is considered a success, then the probability of success is 1/6.
You rolled the dice 3 times. You could either get a six zero times, one time, two times, or all three times. Now you want to get the chances of getting six 1 to 3 times. ( getting six on the top of 1 dice or 2 dices or all three dices when three dices are rolled )
The number of success now lies in a range of 1 to 3 so we use the following BINOM.DIST.RANGE Formula.
=BINOM.DIST.RANGE(3,1/6,1,3)
As a result, we get the answer as 0.42 or 42%. So there are 42 % chances that you are going to get at least one 6 when the dice is rolled 3 times.
Explanation – Here the total number of trials is 3 and the probability of success probability_s is 1/6. The range of getting a six falls from 1 to 3 times therefore the number_s and number_s2 argument is 1 and 3 respectively.
Note that the same result can be obtained by adding the result of these three formulas.
=BINOM.DIST.RANGE(3,1/6,1)
=BINOM.DIST.RANGE(3,1/6,2)
=BINOM.DIST.RANGE(3,1/6,3)
This brings us to the end of BINOM.DIST.RANGE function blog.
Thank you for reading.