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.