Probability is basically a way to measure how likely something is to happen. It’s a number that tells you the chances of an event occurring, calculated by comparing the number of favorable outcomes with the total number of possible outcomes.
Think of flipping a coin – the probability of getting heads is 50%, and the same goes for tails, since there are two possible outcomes. If the weather forecast says there’s an 80% chance of rain, it’s quite likely to rain. Probability is used all the time, from sports and weather forecasts to card games and even predicting the chance of a baby being a certain gender.
While calculating probability might seem difficult, Excel has a built-in function called PROB that makes it much easier. Let’s explore how to use this function to find probabilities in Excel.
Calculating Probability Using the PROB Function
Normally, we calculate probability by dividing the number of successful events by the total possible outcomes. In Excel, the PROB function helps us find the probability of a value falling within a certain range.
The PROB function is a statistical tool that figures out the probability that values from a given range fall within specific limits. The function uses the following syntax:
=PROB(x_range, prob_range, [lower_limit], [upper_limit])
Here’s what each part means:
- x_range: This is the range containing the numeric values for different events. These are the values for which you have probabilities associated with them.
- prob_range: This is the range of probabilities that correspond to the values in
x_range. These probabilities must add up to 1 (or 100% if expressed as percentages). - lower_limit (optional): The minimum value of the event you’re interested in.
- upper_limit (optional): The maximum value of the event you’re interested in. If you don’t include this, the function gives you the probability associated with just the
lower_limitvalue.
Example 1: Ticket Sales Probabilities
Let’s go through an example of how to use the PROB function.
First, you should get your data ready in a table, with two columns: one for the numerical values (like ticket sales) and one for their associated probabilities. The probabilities in the second column should add up to 1 (or 100%).
After entering the data, you can use the SUM function to verify that the probabilities add up to 1. If they don’t, the PROB function will show a #NUM! error.
Now, let’s say you want to know the probability of ticket sales being between 40 and 90. Enter the lower limit (40) and upper limit (90) into your spreadsheet.
Use the following formula in cell B14 to calculate the probability:
=PROB(A3:A9,B3:B9,B12,B13)
In this formula, A3:A9 represents the range of ticket sales numbers, B3:B9 is the range containing the corresponding probabilities, B12 contains the lower limit, and B13 contains the upper limit. The formula will return the probability of 0.39 in cell B14.
To display the result as a percentage, click on the “%” icon under the ‘Home’ tab in the ‘Number’ group. You’ll get 39%, which is the probability of ticket sales falling between 40 and 90.
Probability without an Upper Limit
If you skip the upper limit in the PROB function, it will return the probability that matches the lower limit.
In the example below, the upper limit argument is omitted. The formula then returns 0.12, which is the probability associated with the value of 50.
When formatted as a percentage, this becomes 12%.
Example 2: Dice Roll Probabilities
Now, let’s try a more complex example with dice. Suppose you have two dice and you want to find the probability of getting specific sums when rolling them.
The following table shows the probability of each number appearing on a single die roll.
When you roll two dice, the sum of numbers will be between 2 and 12. The numbers in the red are the sum of numbers from two dice rolls. For example, C3 is the sum of C2 and B3, and so on.
The probability of getting a sum of 2 is only possible when both dice show 1 (1+1). To calculate the chances of each sum, we will use the COUNTIF function.
Create a new table with the possible sum of rolls in one column and their number of occurrences in another column. In cell C11 of this new table, enter the formula:
=COUNTIF($C$3:$H$8,B11)
This formula counts how many times each total roll occurs in the dice roll combinations. Here, $C$3:$H$8 is the range containing all possible sums from two dice, and B11 is the criterion (the specific sum you are counting). The range is locked using absolute references ($) to ensure it stays fixed when the formula is copied.
Drag the formula from C11 down to C21 to count the occurrences for all possible sums.
Now, calculate the individual probabilities of each sum by dividing each count by the total number of possible rolls (36, since 6 x 6 = 36). In the cell next to the count, use the formula:
=C11/36
Copy this formula down to the other cells to get the probabilities of each sum.
You can now see that the sum of 7 has the highest probability.
Now, let’s say you want to find the probability of rolling a total higher than 9. You can use the PROB function:
=PROB(B11:B21,D11:D21,10,12)
Here, B11:B21 is the range of possible sums, D11:D21 is the range of their corresponding probabilities, 10 is the lower limit, and 12 is the upper limit. The function will give 0.17 in cell G14.
This means there’s approximately a 17% chance of rolling a total higher than 9 with two dice.
Calculating Probability Without the PROB Function
It is also possible to calculate probabilities using basic arithmetic without the PROB function.
Usually, the probability of an event happening is calculated as:
P(E) = n(E) / n(S)
Where:
n(E)= The number of times the event occurs.n(S)= The total number of possible outcomes.
For instance, consider two bags of balls, ‘Bag A’ and ‘Bag B’. Bag A contains 5 green, 3 white, 8 red, and 4 yellow balls. Bag B has 3 green, 2 white, 6 red, and 4 yellow balls.
Let’s calculate the probability of picking one green ball from Bag A and one red ball from Bag B at the same time.
To calculate the probability of selecting a green ball from ‘Bag A’, use this formula:
=B2/20
Here, B2 is the number of green balls (5), and 20 is the total number of balls in the bag. Copy this formula to other cells to find the individual probabilities of each color ball in Bag A.
Similarly, calculate the individual probabilities for balls in Bag B using:
=F2/15
Here, the probabilities are shown as percentages.
To find the probability of both events (picking a green ball from Bag A AND a red ball from Bag B) happening together, multiply their individual probabilities:
(probability of picking a green ball from bag A) x (probability of picking a red ball from bag B)
=C2*G3
The result shows a 3.3% chance of simultaneously picking a green ball from Bag A and a red ball from Bag B.