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 categorical variables: 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.
Additional Resources
How to Find Conditional Relative Frequency in a Two-Way Table
What is a Conditional Distribution in Statistics?