The PROB function is a statistical function that can calculate the probability associated with a given range. The function is useful for calculating the probability for the occurrences having discrete probabilities of an event. In this guide, we’re going to show you how to use the PROB function and also go over some tips and error handling methods.
Supported versions
- Excel 2007 and newer versions
PROB Function Syntax
PROB Function Arguments
| x_range | The range of numeric values with associated probabilities. |
| prob_range | The range of probabilities associated with values in x_range. |
| lower_limit | The lower bound of the value for which you want to calculate the probability. |
| [upper_limit] | Optional. The lower bound on the value for which you want to calculate the probability. If omitted, the PROB function returns the probability for the x_range values equal to the lower_limit. |
PROB Function Example
The PROB function can be used in two ways:
- To calculate probability for x_range numbers between lower_limit and [upper_limit].
- To calculate probability for x_range numbers equal to the lower_limit.
Probability between two limits
Our sample contains consecutive integers between 1 and 5 as x_range numbers and associated probability values (prob_range). Each range has 5 cells and probability numbers are between 0 and 1, and add up to 1.
Let’s say we want to calculate the probability between 2 and 4. The formula should like the following:
This formula returns 0.7 because the sum of probabilities between 2 and 4 equals 0.7 (0.2 + 0. 3 + 0.2).
In the Excel file, we have placed limit numbers into the cells to make the process dynamic and we also used named ranges to make formulas easier to read.
Probability for single value
To calculate the probability of a single value, you should not provide an [upper_limit]. Keep in mind that, if there are multiple occurrences of a value in the x_range, the PROB function sums the probability values for all these occurrences.
The following example demonstrates this. Note that there are two 2 values in the x_range with probabilities 0.2 and 0.3. The PROB function with lower_limit 2, returns 0.5.
Tips
- If [upper_limit] is omitted, PROB returns the probability of being equal to lower_limit.
Issues
- All associated probability values should be between 0 and 1. Otherwise, the PROB function returns #NUM!
- The sum of values in the prob_range should be equal to 1. Otherwise, the function returns #NUM!
- If x_range and prob_range ranges are not at same size, the function returns #N/A error value.
