Enrollment for the FMVA™ Certification Program is now OPEN!

PROB Function

Calculates the probability that is associated with a given range

What is the PROB Function?

The PROB Function is categorized under 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) – It is the range of numeric values of x with which there are associated probabilities.
  2. Prob_range (required argument) – It 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) – It is the lower boundary of the value for which we want a probability.
  4. Upper_limit (optional argument) – It is upper boundary of the value for which you want a probability. When we omit this argument, the PROB function ill 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 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 resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!