**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.

A | B | C | D | |
---|---|---|---|---|

1 | Sales | Probability | Formula | Result |

2 | 3 | 0.2 | =PROB(A2:A5,B2:B5,5,10) | 0.8 |

3 | 5 | 0.3 | ||

4 | 10 | 0.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.

A | B | C | D | |
---|---|---|---|---|

1 | Scores | Probability | Formula | Result |

2 | 60 | 0.1 | =PROB(A2:A5,B2:B5,75,85) | 0.5 |

3 | 75 | 0.2 | ||

4 | 85 | 0.3 | ||

5 | 100 | 0.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.

A | B | C | D | |
---|---|---|---|---|

1 | Units Sold | Probability | Formula | Result |

2 | 10 | 0.1 | =PROB(A2:A5,B2:B5,25,35) | 0.4 |

3 | 25 | 0.2 | ||

4 | 35 | 0.3 | ||

5 | 50 | 0.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.

A | B | C | D | |
---|---|---|---|---|

1 | Defective Items | Probability | Formula | Result |

2 | 5 | 0.05 | =PROB(A2:A5,B2:B5,15,25) | 0.3 |

3 | 15 | 0.15 | ||

4 | 25 | 0.35 | ||

5 | 40 | 0.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.

A | B | C | D | |
---|---|---|---|---|

1 | Customer Complaints | Probability | Formula | Result |

2 | 10 | 0.1 | =PROB(A2:A5,B2:B5,20,30) | 0.5 |

3 | 20 | 0.2 | ||

4 | 30 | 0.4 | ||

5 | 50 | 0.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**

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.If you don’t specify

`upper_limit`

, the function will consider it the same as the`lower_limit`

.If the sum of the probabilities exceeds 1,

`PROB`

returns the #NUM! Error value.Make sure the array arguments (

`range`

and`prob_range`

) have the same shape or`PROB`

will return the #N/A error value.You can use named ranges to make your formulas more readable and easier to maintain.

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.