Learning How To Find Probability In Microsoft MS Excel

Microsoft MS Excel is a tool used by many businesses for multiple purposes. Using different mathematical and statistical formulas in Excel is a great feature that eases life by doing complex calculations quickly. Probability is one such important statistical concept used in MS Excel that solves many business-related problems. Probability is a concept of Mathematics, of which most people are scared. But, a good understanding of probability is required to use it efficiently in Excel. In this article, we will explain how to find probability in Excel. But before that, let’s get an understanding of the probability concept.

What Is probability?

Probability is a mathematical concept that defines the possibility of the occurrence of an event. The probability value varies between 0 and 1, where 0 means no probability, and 1 means 100% probability. A higher value means more probability of occurrence of an event.

As an example, when we toss a coin, there are two possible outcomes, i.e., heads and tails. The probability of getting heads while tossing a coin is half, i.e., 50%, so is the probability of getting tails. This is the basic concept of probability, that is used in different applications of mathematics and statistics. The examples include the probability of rain on a specific day, probability of getting the number of sixes in an over in cricket, etc. The formula used to calculate the probability is as below:

Probability (P) = Number of possible occurrences / Total occurrences

Probability Terms:

Random Experiment

A random experiment is a process that gives an outcome whose occurrence is uncertain. To illustrate, rolling a dice, tossing a coin, picking a ball from a bag full of colored balls are random experiments.

Outcome

It is the result of the random experiment, which is not definite. As an example, there can be six possible outcomes when rolling a dice. Each outcome has a probability of occurrence, and the sum of all the probabilities is always 1. Below is the formula used to calculate the probability of each occurrence:

P(E)=1/Count of all the possible outcomes

In our example, the total possible outcomes are 6. Therefore, the probability of each number to appear on dice is 1/6 = 0.17.

Sample Space

All the outcomes of a random experiment together in the form of a set form the sample space. In our example of rolling a dice, {1,2,3,4,5,6} forms the sample space.

Let us try to understand these terms with example:

Random experiment: Rolling a dice

Sample space: {1,2,3,4,5,6}

Outcome: 1,2,3,4,5, and 6 are all the possible outcomes for the random experiment.

Probability of occurrence of 1, i.e., P(occurrence of 1) = 1/6 = 0.17

Similarly, P(occurrence of 2) = 1/6 = 0.17

P(occurrence of 3) = 1/6 = 0.17

P(occurrence of 4) = 1/6 = 0.17

P(occurrence of 5) = 1/6 = 0.17

P(occurrence of 6) = 1/6 = 0.17

Event

An event is defined as the combination of outcomes to define a single characteristic of the random experiment. In the above example, the occurrence of an even number while rolling dice is an event.

Sample space for occurrence of even number = {2,4,6}

Total sample space for the occurrence of any random number = {1,2,3,4,5,6}

P(occurrence of an even number) = P(occurrence of 2) + P(occurrence of 4) + P(occurrence of 6) = 1/6+1/6+1/6 =3/6 = ½ = 0.5

Type of events

The events can be of three types. These types are independent, dependent, and mutually exclusive. Let us understand these events in detail.

1. Independent events: Independent events mean the events are independent of each other and are not affected by the occurrence of other events. For example, tossing a coin three times. The outcome, i.e. heads or tails in each toss are independent of each other.
2. Dependent events: These events are dependent ones, i.e., the occurrence of an event depends upon the occurrence of previous event or events. For example, we draw 3 cards from a deck of 52 cards one after the other. This is an example of a dependent event because when one card is removed from the deck, it is left with 51 cards. Therefore, the probability changes for the second and third card.
3. Mutually exclusive events: Mutually exclusive means either of the events occurs, but not both at the same time. For example, getting head or tail while tossing a coin or getting a number when a dice is rolled. This is mutually exclusive because when a coin is tossed, the outcome can be either heads or tails, but it cannot be both at the same time.

Conditional Probability

The conditional probability of an event is defined as the probability of an event when the other event has already taken place. Conditional probability is the most fundamental concept of probability.

It can be defined with the below formula:

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

where P(A|B) is the find probability of event A when event B has already occurred.

P(A∩B) is the probability of both events A and B together.

P(B) is the probability of event B.

Example of Conditional probability

The probability of the occurrence of rain on a cloudy day is an example of conditional probability. Here, event A denotes the occurrence of rain, and event B denotes it is a cloudy day. This is an example of conditional probability, as we have to find the probability of raining when the day is cloudy.

Consider another example, rolling a dice. When we roll a dice, there is an equal probability of occurrence of any number from 1 to 6. But, if it is already given that number occurred in an even number, then it becomes the conditional probability where we will find the probability of getting a number when we know the number is even.

PROB Function Used in Excel

The probability can be calculated using a PROB function in excel. The PROB method can help in finding the probability for an event having a range of occurrences, each having a discrete probability value of the occurrence of an event.

The PROB function provides the probability value for an event having a range of occurrences, with an upper and lower limit of the occurrence of an event.

Below is the PROB function in excel:

Probability (P) = PROB(x_range, prob_range, [lower_limit], [upper_limit])

where x_range = an array showing different events

prob_range = an array showing the different probabilities of respective events in x_range array

lower_limit = lower limit value of the event

upper_limit = upper limit value of the event

Few points to note about the PROB function:

• If any of the value in array prob_range <= 0 or > 1, then the PROB function will throw as error called #NUM!.
• In case the sum of all the values in array prob_range != 1, then the PROB function will return the error #NUM!.
• If the upper_limit value is left empty in the PROB function, then the PROB function will return the probability according to the lower_limit value.
• If the size of arrays prob_range and x_range is different, then the PROB function throws an error #N/A.

Example of Using PROB Function in Excel

Consider an example of getting the probability of getting a sum of roll ten or more when two dice are rolled.

First of all, we will create a table in the excel showing the sum of the roll and their chances. In our case, we can get any sum of rolls from 2 to 12. There is only a single chance that the sum of 2 dices can be 2. This is only possible when both the dice show 1 when rolled. So, the chance of getting rolls 2 is 1. Similarly, the sum can be 3 when one dice shows 2, and the other shows 1, This can be possible in 2 ways, so the chance for rolls 3 is 2. Similarly, the chance for different rolls can be calculated using the below table:

	Dice 2
Dice 1		1	2	3	4	5	6
	1	2	3	4	5	6	7
	2	3	4	5	6	7	8
	3	4	5	6	7	8	9
	4	5	6	7	8	9	10
	5	6	7	8	9	10	11
	6	7	8	9	10	11	12

Chance can be calculated using the formula COUNTIF(D6:I11,K6), where D6, I11, and K6 denote the cells in excel.

Similarly, the probability can be calculated using Chance/36. Based on these values, the below table gets filled.

	A	B	C
1	Rolls	Chance	Probability
2	2	1	2.78
3	3	2	5.56
4	4	3	8.33
5	5	4	11.11
6	6	5	13.89
7	7	6	16.67
8	8	5	13.89
9	9	4	11.11
10	10	3	8.33
11	11	2	5.56
12	12	1	2.78

Now we will apply the PROB function to calculate the probability of getting a sum of rolls equal to or more than 10. Here x_range=Rolls, prob_range=Chance, lower limit=10, and upper limit=12.

Therefore, P(getting sum of rolls equal to or more than 10) = PROB($A$2:$A$12,$C$2:$C$12,10,12)

Where, $A$2, $A$12, $C$2, and $C$12 denotes the cells in excel.

Conclusion

Probability is an important concept that is used to solve various business as well as real-life problems. Therefore, it is necessary to understand the probability concept clearly. We have explained the probability in detail with an example and how to find the probability using an excel. We hope the information shared in this article is helpful to you and helps you grasp the information. If you want to learn data and related articles, visit cuemath for better insights and a deeper understanding.