Learn how to calculate probability in Excel using the PROB function with or without the upper limit.
What is probability
Probability measures how likely an event is to occur or how likely a proposition is true. The higher the likelihood of an event, the more likely it is that the event will occur.
How to calculate probability in Excel
We measure probability through the ratio of favorable events to the total number of possible occurrences. Excel uses the PROB statistical function to calculate the probability.
For the sake of simplicity, here is the simple definition.
= number of desired outcomes / number of possible outcomes
In the case of six-sided dice, the chance to roll the “6” is = 1/6 = 16.67%.
The PROB function
In this section, we’ll introduce the Excel PROB function. With its help, you can calculate the probability using a built-in spreadsheet function.
Syntax and Function arguments
=PROB(range, prob_range, [lower_limit], [upper_limit])
where
- x_range: the range that contains numerical values
- prob_range: the range that contains probabilities for each corresponding value
- lower_limit [optional]: the lower limit of the values
- upper_limit [optional]; the upper limit of the values
Basic Example to calculate probability in Excel
In the following example, first, prepare our table.
Range C3:C6 contains the number of orders in a period. In range, D3:D6 you can find the probabilities for each order. Finally, the lower limit is 1500, while 2000 is the upper limit in cell G3.
In this case, we’ll use all four arguments of the PROB function.
Type the formula in cell G4:
=PROB(C3:C6, D3:D6, G2, G3)
The result is 55%. What did this mean?
The probability that the number of orders between 1500 and 2000 is 55%.
Tip: If you have faced errors during calculating probability in Excel, take a closer look at your data. Prepare data to avoid further issues: The sum of all probabilities should equal 1 (or 100%). In the example, you can apply this check for column D.
It’s worth double-checking our data: SUM(D3:D6) = 1 = 100%
Calculate the probability without any upper limit
The following example will explain what happens if you are not using upper limits to calculate the probability in Excel.
The PROB function returns the probability that is equal to the selected lower limit.
Use the formula below:
=PROB(C3:C6,D3:D6,G2)
So, without using the same data set without an upper limit, the result is: 2500
Tip: If you use the function without an upper limit, only three arguments are necessary.
Wrapping things up
In some cases, the Excel PROB function returns an error:
- If any value in prob_range is not greater than equal to 0 or greater than 1, the function returns the #NUM! error.
- The sum of the values in the prob_range must be equal to 1. Else PROB functions get the #NUM! error.
- The number of values in the prob_range and the x_range should be equal.
Additional resources:
