# 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.
ABCD
1SalesProbabilityFormulaResult
230.2=PROB(A2:A5,B2:B5,5,10)0.8
350.3
4100.5
• 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.
ABCD
1ScoresProbabilityFormulaResult
2600.1=PROB(A2:A5,B2:B5,75,85)0.5
3750.2
4850.3
51000.4
• 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.
ABCD
1Units SoldProbabilityFormulaResult
2100.1=PROB(A2:A5,B2:B5,25,35)0.4
3250.2
4350.3
5500.4
• 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.
ABCD
1Defective ItemsProbabilityFormulaResult
250.05=PROB(A2:A5,B2:B5,15,25)0.3
3150.15
4250.35
5400.45
• 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.
ABCD
1Customer ComplaintsProbabilityFormulaResult
2100.1=PROB(A2:A5,B2:B5,20,30)0.5
3200.2
4300.4
5500.3
• 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.