Home » How to Calculate Conditional Probability in Excel

How to Calculate Conditional Probability in Excel

by Tutor Aspire

The conditional probability that event occurs, given that event has occurred, is calculated as follows:

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

where:

P(A∩B) = the probability that event and event 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:

Example of a two-way frequency table in Excel

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:

Conditional probabilities in Excel

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?

You may also like