How to Calculate Conditional Probability in Excel

This is the ultimate guide on how to calculate conditional probability in Excel using two easy and efficient methods.

Excel is an excellent tool to use for performing statistical and probability calculations. Since it has many built-in functions and formulas, it makes calculations easier and more efficient. 

And one of the calculations we can perform in Excel is conditional probability. So conditional probability refers to the chances of a particular event occurring given that another event has previously occurred. 

Basically, conditional probability is calculating the probability of event A occurring when event B has occurred. Furthermore, probability is defined as the mathematical representation of how often an event or situation can happen. 

Usually, the conditional probability formula is most often used for a two-way table, meaning two categorical variables must be considered. But, we can perform both one-way and two-way tables in Excel. 

Let’s take a sample scenario wherein we need to calculate the conditional probability in Excel.

Suppose you are conducting a survey about which fast food chain 100 people like best. And you have two categories in your data set, such as the gender of the participant and the fast food chain they chose. So this particular data set is a two-way table. 

Then, we can use the conditional probability formula to calculate the two-way table in Excel.

Great! Now we can move on and dive into a real example of calculating conditional probability in Excel.

 

A Real Example of Calculating Conditional Probability in Excel

Let’s say we have a data set containing two categorical variables. So we first have the rows displaying the gender of the participants. Then, we have the columns which show the fast food chain they chose to like the best. So our initial data set would look like this:

Initial data set

 

Since we have two categorical variables, this is a two-way table. So our first categorical variable is the gender of the participants, which is male and female.

Then, we have our second categorical variable, which is the fast food chains. And we have McDonald’s, KFC, Subway, and Burger King. 

For instance, we want to determine the probability that a participant is female, given that their favorite fast food chain is KFC. So we will utilize the conditional probability formula to answer the question.

So the conditional probability formula is P(A|B) = P(A∩B) / P(A) wherein P(A∩B) refers to the probability that event A and event B will both occur. Then, P(A) refers to the probability that event A will occur. Otherwise, P(B) refers to the probability that event B will occur. 

Given the conditional probability formula, our formula to answer our question would be P(female|KFC) = P(female∩KFC) / P(female). Since we have a total of 100 participants, we will divide the number of females who chose KFC by the total number of participants who chose KFC as their favorite fast-food chain. 

Furthermore, we can calculate the conditional probabilities for other possible situations in the data set using the general formula. 

However, what happens if we have a one-way table? In this case, we have a data set containing the probability of choosing KFC, McDonald’s, or both as the participants’ favorite fast food chains. 

So one event is a participant choosing KFC, while the second event is choosing McDonald’s. And the third event is a participant choosing both KFC and McDonald’s. Thus, we want to calculate the conditional probability of the event that a participant chose KFC after choosing McDonald’s. 

To calculate the conditional probability of a one-way table, we can simply divide the probability of choosing both KFC and McDonald’s by the probability of choosing McDonald’s to obtain the conditional probability value. 

So our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now let’s proceed to learn the steps of how to calculate conditional probability in Excel.

 

How to Calculate Conditional Probability in Excel

In this section, we will discuss the step-by-step process of how to calculate conditional probability in Excel. Furthermore, we will explain how to calculate the conditional probability of a two-way table and a one-way table.

1. Firstly, let’s calculate the conditional probability of a two-way table. In this case, we will utilize the conditional probability formula to calculate the conditional probability that a participant is female, given that their chosen favorite fast food chain is KFC. 

So our formula would be P(female|KFC) = P(female∩KFC) / P(female). Thus, we will input “=(C3/G4)/(C4/G4)”. Then, we will press the Enter key to get the result.

Conditional Probability in Excel

 

2. If we want to round off the conditional probability value to one decimal place, we can simply change the Number Format. To do this, we must select the cell containing the value and right-click. Then, we will select Number Format in the menu.

Number Format menu

 

3. Once the Number Format window opens, we will select Number under the Category section. Next, we will choose 1 for the Decimal places. Lastly, we will click OK to apply the changes.

Conditional Probability in Excel

 

4. And tada! We have successfully calculated the conditional probability in Excel for a two-way table.

Final output

 

5. Secondly, let’s try calculating the conditional probability of a one-way table in Excel. But, we need to first calculate the probability of each event. To do this, we will simply divide the number of participants who chose the specific fast food chain by the total number of participants.

In this case, we will type in the formula “=C4/G4” for the first event. Then, we will press the Enter key to return the result. Afterward, we will continue to calculate the probabilities of the other remaining events.

Conditional Probability in Excel

 

6. Next, we will calculate the conditional probability of a participant choosing KFC after choosing McDonald’s. To do this, we will simply divide the two event probabilities. So we will type in the formula “=J4/J2”. Lastly, we will press the Enter key to return the result.

Conditional Probability in Excel

 

7. And tada! We have successfully calculated the conditional probability for a one-way table in Excel.


Conditional Probability in Excel

 

And that’s pretty much it! We have explained how to calculate the conditional probability for a two-way table and a one-way table in Excel. Now you can apply this method to your work whenever you need to deal with calculating conditional probabilities of events occurring. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

2 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like