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:
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:
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