How to use the BINOM.DIST function
What is the BINOM.DIST function?
The BINOM.DIST function calculates the individual term binomial distribution probability, use this function when
- the success probability is constant through all trials
- you know the number of trials
- the outcome is either a success or failure
- each trial is independent of the other trials.
The BINOM.DIST function was introduced in Excel 2010 and replaces the outdated BINOMDIST function.
Table of Contents
1. Introduction
What is the binomial distribution probability?
The binomial distribution probability gives the likelihood of a specific number of successes occurring in a fixed number of independent trials, each having the same binary success/failure probability.
What is the individual term binomial distribution probability?
The individual term binomial distribution probability is the probability of exactly k successes in n trials with a given success probability p, calculated using combinations to determine the number of ways k successes can occur in those trials.
What are combinations?
A combination is a way of selecting items from a collection where the order of selection does not matter.
For example, if you have three fruits, say an apple, an orange, and a pear. There are three combinations of two that can be drawn from this set:
- apple and a pear
- apple and an orange
- pear and an orange
2. BINOM.DIST function Syntax
BINOM.DIST(number_s,trials,probability_s,cumulative)
3. BINOM.DIST function Arguments
| number_s | Required. The number of successful tests. |
| trials | Required. How many independent tests. |
| probability_s | Required. The probability of success in each test. |
| cumulative | Required. A boolean value. TRUE - cumulative distribution function. FALSE - probability mass function. |
What is cumulative binomial distribution?
The cumulative binomial distribution function gives the probability that a binomial random variable with a given number of trials and success probability will take on a value less than or equal to a specified number of successes x.
What is probability mass function?
A probability mass function is a function that defines a discrete probability distribution by providing the probability that each of a countable number of possible discrete outcomes will occur for a random variable.
What is a binomial random variable?
A binomial random variable is a discrete random variable that represents the number of "successes" in a fixed number of independent binary trials, where each trial has the same probability of success.
What are discrete probabilities?
Discrete probabilities are individual separated probabilities assigned to each of a countable number of possible outcomes that sum to 1, like rolling a die where each number has its own exact probability, as opposed to continuous distributions.
4. BINOM.DIST Function Example 1
The probability that a customer accepts an offer is estimated to be 60%. The offer is given to 20 customers. What is the probability that at most 12 of them accepts the offer?
To solve this we need to use the binomial distribution. It models the number of successes in a fixed number of independent trials (20 customers). Each trial has the same probability of success (0.6 or 60%).
Let x be the random variable representing the number of customers who accepts the offer. Then x follows a binomial distribution with parameters n = 20 (number of trials) and p = 0.6 (probability of success).
The probability we want to find is P(x ≤ 12) which is the cumulative probability of the binomial distribution from 0 up to 12.
Formula in cell C21:
The formula returns approx. 0.5841, in other words, the probability is 58.4% that up to 12 customers accepts the offer.
The chart in the image above shows an orange line representing the cumulative probability. Go to 12 on the x-axis and find where the orange line intersects the middle of the column. The secondary y-axis to the right shows a value just below 0.6 which seems to match the calculated number 0.5841
5. BINOM.DIST Function Example 2
There are 22 machines that operate independently of each other in a factory. The probability of a breakdown occurring during a day is 0.1 for each of the machines. What is the probability that three machines will stop during a certain day?
The binomial distribution is what we need in this example. It models the number of successes (machine breakdowns) in a fixed number of independent trials (22 machines), where each trial has the same probability of success (0.1 or 10%).
Let x be the random variable representing the number of machines that break down during the day. Then x follows a binomial distribution with parameters n = 22 (number of machines) and p = 0.1 (probability of breakdown for each machine).
The probability we want to find is P(X = 3), which is the probability mass function of the binomial distribution evaluated at 3.
The formula returns 0.208, in other words, the probability is 20.8% that exactly 3 machines break down during a day.
The chart in the image above shows blue chart columns representing the probability mass function of the binomial distribution. Go to 3 on the x-axis and find the value value for that column. The y-axis to the left shows a value just above 0.2 which seems to match the calculated number 0.208
6. BINOM.DIST Function Example 3
There are 30 students in a class. There is a 50% risk that each student, independently of each other, will catch a harmless but highly contagious cold. What is the chance that 12 students or less will attend school on the same day?
We need to find the probability that 12 or fewer students will not catch the cold, which is the same as the probability that 18 or more students will catch the cold.
- Total number of students: 30
- Probability of catching the cold for each student: 0.5 (or 50%)
We want to find P(X ≥ 18), which is the probability that 18 or more students will catch the cold.
P(X ≥ 18) = 1 - P(X ≤ 17)
We can calculate P(X ≤ 17) using the cumulative distribution function of the binomial distribution and then take the complement to find P(X ≥ 18).
Formula in cell C21:
The formula in cell C21 returns 0.181 which is 18.1%. This means that there is a 18.1% chance that 12 students or less will attend school on the same day.
7. BINOM.DIST function not working
The BINOM.DIST function returns
- #VALUE! error value if number_s, trials or probability_s argument is non-numeric.
- #NUM! error value if:
- number_s <= 0 (zero)
- number_s > trials
- probability_s < 0 (zero)
- probability_s > 1
- A = B
number_s and trials are converted into integers.
'BINOM.DIST' function examples
The following article has a formula that contains the BINOM.DIST function.
Table of Contents How to use the BETADIST function How to use the BETAINV function How to use the BINOMDIST […]
Functions in 'Statistical' category
The BINOM.DIST function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form