Table of Contents
Conditional probability in Excel can be calculated using the function “=IF” combined with the function “=COUNTIF”. The formula requires two ranges of values and two criteria. The first range is the set of outcomes and the second range is the set of conditions. The first criteria is the value of the outcome and the second criteria is the value of the condition. The result of the formula is the probability of the outcome given the condition.
The conditional probability that event A occurs, given that event B has occurred, is calculated as follows:
P(A|B) = P(A∩B) / P(B)
where:
P(A∩B) = the probability that event A and event B both occur.
P(B) = the probability that event B occurs.
This formula is particularly useful when calculating probabilities for a two-way table, which is a table that displays the frequencies (or “counts”) for two categorical variables.
For example, the following two-way table shows the results of a survey that asked 300 people which sport they liked best: baseball, basketball, football, or soccer. The rows display the gender of the respondent and the columns show which sport they chose:
This is a two-way table because we have two : gender and favorite sport.
Next, we’ll show how to calculate conditional probabilities for two-way tables in Excel.
How to Calculate Conditional Probability in Excel
Suppose we’re interested in answering questions like:
“What is the probability that a respondent is male, given their favorite sport is baseball?”
We can find the answer by using the conditional probability formula:
P(male|baseball) = P(male∩baseball) / P(baseball) = (34/300) / (68/300) = 0.5
Thus, the probability that a respondent is male, given their favorite sport is baseball, is 0.5 (or 50%).
We can calculate conditional probabilities for other scenarios in the table using a similar formula. The image below shows how to calculate every conditional probability in the table, along with the formula used:
Notice that for every conditional probability calculation, we’re simply using the conditional probability formula of P(A|B) = P(A∩B) / P(B).
For example, the probability that a respondent’s favorite sport is soccer, given they are female, is calculated as:
P(soccer|female) = P(soccer∩female) / P(female)
Out of the 300 respondents, there are exactly 44 who are female and prefer soccer as their favorite sport, thus P(soccer∩female) = 44/300.
And out of the 300 respondents, there are 150 who are female, thus P(female) = 150/300.
Thus, P(soccer|female) = P(soccer∩female) / P(female) = (44/300) / (150/300) = 0.2933.
We perform a similar calculation for every conditional probability scenario.