How to Use the PROB Function in Google Sheets to Calculate Discrete Probability Ranges

When analysing finite discrete events in Google Sheets—such as dice rolls, defect distributions, customer purchase volume frequencies, or project completion durations—you frequently need to calculate the likelihood that an outcome falls within a specific numerical boundary. While you can construct complex combinations of SUMIFS or nested array formulas, Google Sheets includes a dedicated statistical function purpose-built for this exact calculation: PROB.

The PROB function calculates the cumulative probability associated with discrete values lying between a lower limit and an optional upper limit, provided you supply the corresponding set of probabilities for each possible event.

Syntax and Argument Structure

The syntax for the PROB function is structured as follows:

=PROB(data_range, probabilities_range, lower_limit, [upper_limit])

Each argument plays a specific mathematical role:

• data_range: The range or array of discrete numerical values representing all possible outcomes.
• probabilities_range: The corresponding range of individual probabilities for each outcome in data_range.
• lower_limit: The lower numerical bound of values you want to sum probabilities for (inclusive).
• upper_limit (Optional): The upper numerical bound of values you want to sum probabilities for (inclusive). If omitted, PROB calculates the exact probability of matching only the lower_limit.

Strict Data Validation Constraints

To avoid formula errors, ensure your dataset conforms to the following probability axioms:

• Equal Range Dimensions: The data_range and probabilities_range must contain the exact same number of cells. If dimensions differ, Google Sheets returns a #N/A error.
• Sum of Probabilities Must Equal 1: The sum of all values in probabilities_range must equal 1.0 (100%). If the sum is less than or greater than 1 (allowing for negligible floating-point rounding), PROB returns a #NUM! error.
• Positive Individual Probabilities: No individual probability in probabilities_range can be negative (< 0) or exceed 1.0 (> 1). Violating this condition triggers a #NUM! error.
• Upper Limit Rule: If provided, upper_limit must be greater than or equal to lower_limit. Specifying an upper limit less than the lower limit returns 0.

Benchmark Examples: Discrete Probability Modelling

Consider a customer service department modelling daily call arrival volumes based on historical frequency records:

Call Volume (x)	Observed Probability P(x)	Formula Example	Calculated Output	Operational Interpretation
10	0.10	=PROB(A2:A6, B2:B6, 10)	0.10 (10%)	Exact probability of receiving exactly 10 calls.
20	0.25	=PROB(A2:A6, B2:B6, 20, 30)	0.60 (60%)	Probability of receiving between 20 and 30 calls inclusive (0.25 + 0.35).
30	0.35	=PROB(A2:A6, B2:B6, 30, 50)	0.65 (65%)	Likelihood of call volume reaching 30 or more calls.
40	0.20	=PROB(A2:A6, B2:B6, 0, 20)	0.35 (35%)	Probability of receiving 20 or fewer calls (low volume day).
50	0.10	=SUM(B2:B6)	1.00 (100%)	Total probability sum satisfies mathematical validation.

Step-by-Step Implementation Guide

1. In column A (cells A2:A6), enter your discrete outcome values (e.g., 10, 20, 30, 40, 50).
2. In column B (cells B2:B6), enter your relative probabilities (e.g., 0.10, 0.25, 0.35, 0.20, 0.10).
3. Verify that your probabilities sum to exactly 1:

   =ROUND(SUM(B2:B6), 4) = 1

4. In cell D2, calculate the probability of an outcome between 20 and 40:

   =PROB(A2:A6, B2:B6, 20, 40)

   Result: 0.80 (or 80%).

5. In cell D3, calculate the probability of getting exactly 30:

   =PROB(A2:A6, B2:B6, 30)

   Result: 0.35 (or 35%).

Frequently Asked Questions

Can the PROB function handle continuous distributions?

No. PROB is designed strictly for discrete distributions where each outcome has an explicit probability mass assigned in a lookup table. For continuous distributions, use functions such as NORM.DIST, EXPON.DIST, or BETA.DIST.

What happens if a lower limit does not match an exact number in data_range?

PROB evaluates all entries in data_range that satisfy lower_limit <= value <= upper_limit. Even if the exact boundary values are not present in your data table, any intermediate observations falling inside that numerical range are summed.