PROB Function in Excel

Part 1: Introduce

PROB Function in Microsoft Excel

• Definition: The PROB function in Excel is a Statistical function that returns the probability that values in a range fall between two limits. If the upper limit is not supplied, it is assumed to be the same as the lower limit.

• Purpose: It determines a variable’s probability in a particular range in a data set.

• Syntax: The syntax for the PROB function in Excel is =PROB(range, prob_range, lower_limit, [upper_limit])

  • Range: This is an array of possible x values or outcomes.
  • Prob_range: This is an array of probabilities associated with each outcome in the range.
  • Lower_limit: This is the minimum value in the criteria.
  • Upper_limit (Optional): This is the maximum value in the criteria. If omitted, the function will return the probability of being equal to the lower_limit.

• Return value: PROB function will return the probability that variables in a range fall between the two specified limits.

• Remarks: The values in the Prob_range argument should sum to 1, else the function will return an error. If a value in the prob_range argument is < 0 or > 1, the function will return an error. If the sum of the probabilities is greater than 1, PROB returns the #NUM! Error value.

Part 2: Examples

Example 1

• Purpose of Example: To calculate the probability of making 5 to 10 sales.

• Explanation: Based on our historical data, we want to calculate the likelihood of making between 5 to 10 sales. The result of 0.8 means an 80% probability of making between 5 and 10 sales.

Example 2

• Purpose of Example: To calculate the probability of scoring between 75 to 85 on a test.

• Explanation: In this case, we want to calculate the probability of scoring between 75 and 85 based on historical test score data. The result of 0.5 implies a 50% probability of scoring between 75 and 85.

Example 3

• Purpose of Example: To calculate the probability of selling between 25 to 35 units of a product.

• Explanation: In this case, we want to calculate the probability of selling between 25 and 35 units based on historical sales data. The result 0.4 indicates a 40% probability of selling between 25 and 35 units.

Example 4

• Purpose of Example: To estimate the probability of a machine producing between 15 to 25 defective items.

• Explanation: Based on past production data, we are calculating the possibility of the machine producing between 15 and 25 defective items. The result, 0.3, indicates a 30% chance of this occurring.

Example 5

• Purpose of Example: To determine the probability of receiving between 20 to 30 customer complaints.

• Explanation: Based on historical complaint data, we estimate the likelihood of receiving between 20 to 30 customer complaints. The result, 0.5, suggests a 50% probability of this event.

Part 3: Tips and Tricks

1. Remember that the values in the prob_range argument should sum to 1, else the function will return an error. If a value in the prob_range argument is < 0 or > 1, the function will return an error.

2. If you don’t specify upper_limit, the function will consider it the same as the lower_limit.

3. If the sum of the probabilities exceeds 1, PROB returns the #NUM! Error value.

4. Make sure the array arguments (range and prob_range) have the same shape or PROB will return the #N/A error value.

5. You can use named ranges to make your formulas more readable and easier to maintain.

6. The PROB function was replaced with newer functions in Excel 2010. It’s still available for compatibility, but use the more unique functions if possible for better results.