How to use PROB Function in Excel

The PROB function returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit.

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

The PROB function syntax has 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.

Example: Let’s look at some Excel PROB function examples and explore how to use the PROB function as a worksheet function in Microsoft Excel:

Suppose we are given the data below:

PROB Function - How to use PROB Function in Excel

Syntax:  =PROB(A2:A11,B2:B11,A2)

Result: 0.3

PROB Function 1 - How to use PROB Function in Excel

Based on the Excel spreadsheet above, the following PROB examples would return:

Syntax: =PROB(A2:A11,B2:B11,700)
Result: 0.25

Syntax: =PROB(A2:A11,B2:B11,C4,D4)
Result: 0.35

Syntax: =PROB(A2:A11,B2:B11,C5,D5)
Result: 0.5

Syntax: =PROB(A2:A11,B2:B11,C6,D6)
Result: 0.75

Syntax: =PROB(A2:A11,B2:B11,C7,D7)
Result: 0.22

Syntax: =PROB(A2:A11,B2:B11,1100,1400)
Result:0.2

Note:

  • If any value in prob_range ≤ 0 or if any value in prob_range > 1, PROB returns the #NUM! error value.
  • If the sum of the values in prob_range is not equal to 1, PROB returns the #NUM! error value.
  • If upper_limit is omitted, PROB returns the probability of being equal to lower_limit.
  • If x_range and prob_range contain a different number of data points, PROB returns the #N/A error value.

 

Add a Comment

Your email address will not be published. Required fields are marked *