How to calculate probability in Excel

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.

data table

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)

Calculate the probability without upper limit

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:

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.