How to Use the Geometric Distribution in Excel


The geometric distribution 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 random variable X follows a geometric distribution, then the probability of experiencing 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:

Geometric distribution in Excel

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.

Additional Resources

An Introduction to the Geometric Distribution
Geometric Distribution Calculator
5 Real-Life Examples of the Geometric Distribution

Leave a Reply

Your email address will not be published.