Contact
12 programs
Explore the site

Zoho Sheet PROB: Probability Ranges from a Discrete Table

Statistical Intermediate Zoho Sheet

Use Zoho Sheet PROB to add probabilities across a discrete value range, with validated examples, input checks and compatibility notes.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked

Quick answer: PROB evaluates the probability associated with a selected range of discrete numeric outcomes, using a separate array of probabilities that correspond to those outcomes. With values 0, 1, 2, 3, 4 and respective probabilities 0.1, 0.2, 0.4, 0.2, 0.1, the mathematical probability of an outcome from 1 to 3 inclusive is 0.8. The candidate Zoho formula is =PROB(A2:A6;B2:B6;1;3). Its name, array order and two bound positions are directly documented by Zoho; 0.8 was calculated independently, not natively in Zoho Sheet. [Z-PROB, M1]

Zoho’s published syntax and what each argument means

PROB(values; probabilities; [start]; [end])
Position Argument Published requirement Input expectation
1 values Required Array/range of possible numeric outcomes, such as A2:A6.
2 probabilities Required Corresponding probabilities in a range/array of the same size as values; each corresponds to the value in the same position. Zoho says the probabilities must sum to 1.
3 start Bracketed as optional by Zoho Lower numeric threshold of the outcomes of interest. Use an explicit value for examples because Zoho does not explain the result when start is omitted.
4 end Bracketed as optional by Zoho Upper numeric threshold. A three-argument form appears in Zoho’s examples, but detailed omitted-end semantics are not spelled out.

The exact order matters: outcomes first, probabilities second, then lower and upper bounds. The user-supplied probabilities—not the number of matching rows—determine the result. Don’t use a percentage like 20 in the probability column where 0.20 is intended. Zoho documents 0–1 as the range for the probability entries, and a #NUM! condition when their total is not 1. [Z-PROB]

Optional-argument caution: the page encloses start and end in square brackets, but none of its published worked examples omit both bound arguments. It shows the three-argument form =PROB(A2:A5;B2:B5;C2) returning 0.2, yet does not unambiguously describe equality-versus-range semantics when end is absent. This guide therefore illustrates the core inclusive-interval mathematics with both bounds supplied, and treats one-bound behavior as a native verification item rather than importing Excel’s rule as proven Zoho behaviour. [Z-PROB, MS-PROB]

Reproducible discrete probability table

Enter this example exactly in cells A1:B6. The values could represent a count of delayed packages per shift under a hypothetical probability model. The probabilities are deliberately simple decimals adding to one. [M1]

Row A: delayed packages B: probability
1 Delay count Probability
2 0 0.10
3 1 0.20
4 2 0.40
5 3 0.20
6 4 0.10

Check the total in B7 using =SUM(B2:B6). It should mathematically total 1.00. Each probability corresponds to its associated A value; sorting A alone without carrying B with it will invalidate the model, even if the numerical sum stays 1. [M1]

Task Candidate Zoho formula Independently expected probability
Between 1 and 3 inclusive =PROB(A2:A6;B2:B6;1;3) 0.80
Between 0 and 2 inclusive =PROB(A2:A6;B2:B6;0;2) 0.70
Exactly 2 (both limits explicitly equal) =PROB(A2:A6;B2:B6;2;2) 0.40
Exactly 4 (both limits explicitly equal) =PROB(A2:A6;B2:B6;4;4) 0.10
Any listed value 0–4 =PROB(A2:A6;B2:B6;0;4) 1.00
Between 2 and 3 inclusive =PROB(A2:A6;B2:B6;2;3) 0.60
Impossible interval 5–6 =PROB(A2:A6;B2:B6;5;6) 0.00 (mathematical model; native behavior not checked)

To make the bounds editable, enter lower bound 1 in D2 and upper bound 3 in E2, and in F2 type:

=PROB($A$2:$A$6;$B$2:$B$6;D2;E2)

Changing D2/E2 allows a small what-if model without rewriting the probability table. Absolute references keep the probability mapping fixed if you fill the formula down a list of scenarios. Use =SUM(B2:B6) to audit total weight after edits. No separate probability range needs to be normalized if it already sums to 1. [M1]

How to calculate the result independently

For a discrete table with values x_i and probability weights w_i, the mathematical interval probability is:

P(L ≤ X ≤ U) = sum of w_i over entries where L ≤ x_i ≤ U.

For the 1–3 interval, sum 0.20+0.40+0.20=0.80. The value labels are not uniformly weighted unless the probability table says they are. If one value appears multiple times, whether to pre-aggregate identical outcomes or treat each row as a distinct elementary outcome is a data-model decision; don’t assume a function deduplicates entries unless verified natively. [M1]

An independent worksheet check can be built without PROB. Add column C with =IF(AND(A2>=$D$2;A2<=$E$2);B2;0) in C2 and fill through C6, then use =SUM(C2:C6). Here D2 and E2 contain the chosen bounds. This is a suggested formula comparison using individually documented Zoho functions (IF, AND, SUM), not evidence that the direct PROB implementation was natively exercised. Under the mathematical model it adds the same included probabilities. [Z-PROB, M1]

Worked business example: delay-count risk

Suppose the values 0–4 represent an illustrative forecast distribution of delayed orders per shift. To ask, “What is the chance of at most two delays?”, enter:

=PROB(A2:A6;B2:B6;0;2)

The independent expected value is 0.7, or 70%. To ask, “What is the probability of three or more delays?”, with the table covering all possible outcomes, use:

=PROB(A2:A6;B2:B6;3;4)

The independent expected value is 0.3. A complementary arithmetic check is 1-0.7=0.3; this works only if the table includes all possible outcomes and the weights genuinely sum to 1. If delays beyond 4 can happen but are absent from the table, the probabilities describe a truncated or normalized scenario, not the true business risk. [M1]

To calculate the expected number of delays, the mathematical expectation is 0(0.1)+1(0.2)+2(0.4)+3(0.2)+4(0.1)=2. A candidate calculation is =SUMPRODUCT(A2:A6;B2:B6), which is conceptually different from PROB: it weights each value rather than adding only weights in an interval. A model can have the same expectation as another model but very different tail risk. [M1]

Advanced checks: empirical frequencies and calibration

If your source data are observed delay counts rather than known theoretical probabilities, first create a defensible probability table. For mutually exclusive outcomes, divide each count by the total number of observations so all weights sum to one. Decide in advance whether a zero-frequency but plausible outcome belongs in the distribution; the error policy for a zero probability entry is not fully specified in Zoho’s PROB article. Model validation should compare predicted interval frequencies against later observations. [Z-PROB, M1]

The probabilities column must be in the same row order as the outcomes. If you swap the 0.4 probability at value 2 with the 0.1 probability at value 4, the sum remains 1 but the probability of 1–3 changes from 0.8 to 0.5. This is a more subtle failure than an obvious formula error. [M1]

PROB is useful for a custom discrete probability distribution. By contrast, BINOMDIST(k;n;p;[mode]) assumes a binomial process with its own model parameters, and CRITBINOM(n;p;a) finds a binomial count quantile; they are not interchangeable with a manually supplied probability table. For example, if you replace the custom table with a binomial mass table, the cross-check must cover the same full support and use the same model parameters before comparing answers. [Z-BINOM, Z-CB, M1]

Errors, edge cases and import pitfalls

Condition What Zoho documents / recommended response
#NUM! for invalid probability entries Zoho says entries outside 0–1 are invalid. Use fractions, not whole percentage numbers. [Z-PROB]
#NUM! if probabilities do not sum to 1 Zoho explicitly lists a non-unit total. Validate with =SUM(B2:B6). The precise floating-point tolerance is not documented; do not invent one. [Z-PROB]
Mismatched array lengths Zoho says the probabilities range must be the same size as the values range, but does not provide a unique PROB-specific error code for the mismatch in this article. Fix both ranges rather than promising a particular code. [Z-PROB]
Lower bound greater than upper bound The mathematical interval is empty; the page does not specify Zoho’s actual output/error behavior for reversed bounds. Swap or validate the bounds before the formula. [Z-PROB, M1]
Missing lower bound The official syntax brackets it as optional but leaves behavior unclear. Use explicit lower and upper bounds until native-tested. [Z-PROB]
End omitted Zoho gives three-argument examples but does not fully define equality semantics; do not assert the Excel fallback as Zoho evidence. [Z-PROB, MS-PROB]
#VALUE! Zoho’s generic error table describes incorrect argument data types. Remove nonnumeric values or convert intentional numeric text. [Z-PROB]
#NAME! Confirm spelling PROB and the documented function syntax. [Z-PROB]
#REF! Repair deleted/moved references. [Z-PROB]
#N/A! Zoho’s generic table uses lookup as an example; it does not establish a PROB-specific array-mismatch code. [Z-PROB]
Unexpected zero Check the interval bounds and that the possible outcome labels are numbers instead of numerically-looking text. [M1]

Vendor comparison, versions and locale

Zoho’s documented PROB(values; probabilities; [start]; [end]) closely resembles Microsoft Excel’s PROB(x_range,prob_range,[lower_limit],[upper_limit]). However, Microsoft explicitly documents an omitted-upper-limit exact-value rule and zero-probability error conditions, while the Zoho article does not spell those out. Do not promote those Excel-specific details into asserted Zoho engine behaviour. Likewise, Zoho’s page does not provide a first-supported version, current subscription-tier matrix, mobile/desktop calculation parity guarantee, or array-size upper bound. These are unverified—not reasons to invent compatibility labels. [Z-PROB, MS-PROB]

The Zoho locale preferences page confirms user/workbook control over number presentation, such as decimal/grouping format; numeric imports should be rechecked after locale changes. The semicolon-separated syntax shown here comes from the English Zoho function article. No browser, account or locale-specific native workbook was used in this run. [Z-LOCALE, Z-PROB]

Native validation: Verify omitted bounds, reversed intervals, zero probabilities, normalization tolerance, array lengths and locale behavior in your own Zoho workbook before relying on undocumented cases.