How to Apply Bayes’ Theorem in Excel


Bayes’ Theorem states the following for any two events A and B:

P(A|B) = P(A)*P(B|A) / P(B)

where:

  • P(A|B): The probability of event A, given event B has occurred.
  • P(B|A): The probability of event B, given event A has occurred.
  • P(A): The probability of event A.
  • P(B): The probability of event B.

For example, suppose the probability of the weather being cloudy is 40%.  Also suppose the probability of rain on a given day is 20% and that the probability of clouds on a rainy day is 85%. 

If it’s cloudy outside on a given day, what is the probability that it will rain that day?

Solution:

  • P(cloudy) = 0.40
  • P(rain) = 0.20
  • P(cloudy | rain) = 0.85

Thus, we can calculate:

  • P(rain | cloudy) = P(rain) * P(cloudy | rain) / P(cloudy)
  • P(rain | cloudy) = 0.20 * 0.85 / 0.40
  • P(rain | cloudy) = 0.425

If it’s cloudy outside on a given day, the probability that it will rain that day is 0.425 or 42.5%.

The following example shows how to solve this exact problem using Bayes’ Theorem in Excel.

Example: Bayes’ Theorem in Excel

The following formula shows how to apply Bayes’ Theorem in Excel:

Bayes' theorem formula in Excel

For example, if we know the following probabilities:

  • P(cloudy) = 0.40
  • P(rain) = 0.20
  • P(cloudy | rain) = 0.85

Then we can simply plug these into the cells in Excel:

Bayes' rule example in Excel

This tells us that if it’s cloudy outside on a given day, the probability that it will rain that day is 0.425 or 42.5%.

Additional Resources

How to Calculate Conditional Probability in Excel
How to Apply the Empirical Rule in Excel
How to Apply the Central Limit Theorem in Excel

One Reply to “How to Apply Bayes’ Theorem in Excel”

  1. If there is smoke, there is fire. If there is rain, there are clouds. Therefore P(clouds|rain)=1 and cannot be 0.85.

Leave a Reply

Your email address will not be published. Required fields are marked *