PROB Function

Calculates the probability that is associated with a given range

What is the PROB Function?

The PROB Function[1] is categorized under Excel Statistical functions. It will calculate the probability that is associated with a given range.

In financial analysis, the PROB function can be useful in estimating business losses. It can also be used by a risk manager to carry out financial probability analysis.

 

Formula

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

 

The PROB function uses the following arguments:

  1. X_range (required argument) – This is the range of numeric values of x with which there are associated probabilities.
  2. Prob_range (required argument) – This is the set (array) of probabilities that is associated with values in x_range. The array must be of the same length as the x_range array and the values in prob_range must add up to 1.
  3. Lower_limit (optional argument) – This is the lower boundary of the value for which we want a probability.
  4. Upper_limit (optional argument) – This is upper boundary of the value for which you want a probability. When we omit this argument, the PROB function will simply return the probability associated with the value of the supplied lower_limit.

 

How to use the PROB Function in Excel?

To understand the uses of PROB function, let’s consider a few examples:

 

Example 1

Suppose we are given the data below:

 

PROB Function

 

The probability for the given range when the lower limit is set to 50 and the upper limit is 80 would be:

 

PROB Function - Example 1

 

Example 2

Let’s assume we are given two dice and we wish to find the probability of getting a roll of 10 or higher.

The possible outcomes when we roll two dice are:

 

PROB Function - Example 2

 

When we roll dice 1 and dice 2, the chance of getting 2 is possible only when we get 1 on both dice, so chance = 1.

To get all chances, we can make an array that looks like the one below:

 

PROB Function - Example 2a

 

We listed the numbers 1 to 6 and then in cell D6, we added the value of C6 and D5. Similarly, for D7 it is the sum of C7 and D6.

Now for calculating chances, we can use the formula below:

 

PROB Function - Example 2b

 

The COUNTIF function is used wherein the array is given as the range and the criteria argument is the roll.

For probability, we divided the chances by 36, as there are 6 x 6 = 36 possible rolls.

 

PROB Function - Example 2c

 

The table will look like this:

 

PROB Function - Example 2d

 

Now let’s calculate the probability of getting a roll higher than 10 using the PROB function. The formula to use is:

 

PROB Function - Example 2e

 

We get the result below:

 

PROB Function - Example 2f

 

We can cross-check the figures by adding the individual probability calculated: (8.33%+5.56%+2.78%) = 16.67%.

 

Few things to remember about the PROB Function

  1. #NUM! error – Occurs if either:
    • Any value in the given prob_range is less than 0 or greater than 1; or
    • The values in the given prob_range do not add up to 1.
  2. N/A! error – Occurs if the given x_range and prob_range arrays are of different lengths (i.e., contain different numbers of data points).
  3. If we omit the upper_limit, PROB returns the probability of being equal to the lower_limit.
  4. The PROB function was introduced in MS Excel 2007 and hence is unavailable in earlier versions.

 

Click here to download the sample Excel file

 

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

Article Sources

  1. PROB Function
0 search results for ‘