Table of Contents
The Geometric Distribution is a probability distribution that can be used in Excel to calculate the probability of a given number of events occurring within a fixed number of trials. It is useful for analyzing things like the probability of a coin landing heads up after a certain number of tosses or the probability of a person winning a certain number of games in a row. To use the Geometric Distribution in Excel, you need to use the function GEOM.DIST to calculate the probability of a success in a given trial. You can also use the function BINOM.DIST to calculate the cumulative probability of a given number of successes within the same trial.
The describes the probability of experiencing a certain amount of failures before experiencing the first success in a series of Bernoulli trials.
A Bernoulli trial is an experiment with only two possible outcomes – “success” or “failure” – and the probability of success is the same each time the experiment is conducted.
An example of a Bernoulli trial is a coin flip. The coin can only land on two sides (we could call heads a “success” and tails a “failure”) and the probability of success on each flip is 0.5, assuming the coin is fair.
If a X follows a geometric distribution, then the probability of experiencing k failures before experiencing the first success can be found by the following formula:
P(X=k) = (1-p)kp
where:
- k: number of failures before first success
- p: probability of success on each trial
The following examples show how to calculate probabilities related to the geometric distribution in Excel.
Example 1: Flipping a Coin
Suppose we are flipping a coin and we want to know the probability that it will take exactly three “failures” until a coin finally lands on heads.
We would use the following formula to calculate this probability:
The probability that we will experience three “failures” until a coin finally lands on heads is .0625.
Example 2: Shooting Free Throws
Suppose a certain basketball player makes 60% of his free throws. What is the probability that the player will miss four free throws until he finally makes one?
We would use the following formula to calculate this probability:
The probability that the player will miss four free throws until he finally makes one is .01536.
Example 3: Supporting a Law
Suppose a researcher is waiting outside of a library to ask people if they support a certain law. The probability that a given person supports the law is p = 0.2. What is the probability that the fourth person the researcher talks to is the first person to support the law?
We would use the following formula to calculate this probability:
The probability that the fourth person the researcher talks to is the first person to support the law is .1024.