The Excel PROB function returns the probability that values fall within a range, using a list of outcomes and their associated probabilities — effectively reading a discrete probability table.
Syntax
| Argument | Description | |
|---|---|---|
x_range | Required | The range of distinct numeric outcomes (e.g. possible values 1, 2, 3, 4, 5). |
prob_range | Required | The probabilities matching each outcome in x_range. They must be greater than 0, at most 1, and sum to 1. |
lower_limit | Required | The lower bound of the outcomes you want the probability for. |
upper_limit | Optional | The upper bound. If omitted, PROB returns the probability of exactly lower_limit. |
How to use it
PROB works from a discrete distribution you supply: a column of outcomes (x_range) paired with their probabilities (prob_range). It adds up the probabilities of every outcome between lower_limit and upper_limit.
| A | B | |
|---|---|---|
| 1 | Outcome | Probability |
| 2 | 1 | 0.1 |
| 3 | 2 | 0.2 |
| 4 | 3 | 0.3 |
| 5 | 4 | 0.25 |
| 6 | 5 | 0.15 |
If you omit upper_limit, PROB returns the probability of exactly lower_limit. The probabilities in prob_range must sum to 1, or PROB returns the #NUM! error.
Probabilities must total 1. Every value in prob_range must be greater than 0 and no more than 1, and together they must sum to exactly 1 — otherwise PROB returns #NUM!.
Try it: interactive demo
Pick a PROB example to see the formula and its result.
Practice workbook
Frequently asked questions
What happens if I omit the upper_limit in PROB?
lower_limit value. So =PROB(A2:A6,B2:B6,3) returns the probability tied to the outcome 3 alone.Why does PROB return a #NUM! error?
prob_range do not sum to 1, or some probability is ≤0 or >1. Check that your distribution is complete and normalized.Do x_range and prob_range have to be the same size?
x_range must line up with exactly one probability in prob_range; mismatched sizes return the #N/A error.How is PROB different from a distribution function like BINOM.DIST?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class