PROB

What it does

PROB(x_range, prob_range, lower_limit, [upper_limit])

PROB(x_range, prob_range, lower_limit, [upper_limit]) treats its two vector arguments as a finite discrete probability distribution and returns the mass that distribution assigns to a closed interval.

Write the paired vectors as (x_1, p_1), …, (x_n, p_n). They define the discrete measure

mu  =  sum_{i=1..n}  p_i * delta_{x_i}

on the real line, where delta_x is the unit point mass at x. PROB returns

PROB(x, p, a, b)  =  mu([a, b])  =  sum over { i : a <= x_i <= b } of p_i

Two features of that definition carry the page:

1. The interval is closed at both ends. The accumulation admits x_i when a <= x_i and x_i <= b. So PROB computes P(a <= X <= b), not P(a < X <= b) and not P(a <= X < b). On a discrete distribution the difference is a whole atom, not a measure-zero technicality: it is exactly p at the endpoint.

The range of the function is [0, 1], and it is monotone in the interval: widening [a, b] never decreases the result. PROB is a probability lookup, not a distribution fit — it does no smoothing, no interpolation between the x_i, and no normalization. A value of lower_limit that lies strictly between two atoms contributes nothing.

x_range is not required to be sorted, and the atoms are not required to be distinct. If the same x appears twice with different probabilities, both are admitted and both are counted; PROB sums over index positions, not over distinct support points.

Arguments

The pairing is positional, element by element in row-major order over each argument. That is the whole of the correspondence: x_range and prob_range are two columns of one table, and nothing checks that they were meant to line up beyond their having the same number of elements.

prob_range is documented to be a genuine probability vector: each entry in (0, 1], and the entries summing to 1. Both conditions are stated by Microsoft as #NUM! conditions rather than as advice, which makes PROB unusually strict for a worksheet function — most of Excel will compute happily on nonsense.

lower_limit and upper_limit are ordinary numeric slots subject to coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number in [0, 1].

• No atom falls in the interval. The sum over an empty index set is 0. PROB returns zero, not an error and not #N/A. A caller cannot distinguish "the interval genuinely has zero mass" from "the interval missed every atom" — they are the same statement about the distribution.
• Errors in the vectors propagate as themselves.

How to implement

Numerical notes

PROB looks arithmetically trivial and is not, for one reason: it contains an equality test against 1 in disguise.

The accumulation itself is a plain left-to-right sum of at most n doubles, each in [0, 1]. There is no cancellation — every term is non-negative — so the relative error of the returned mass is bounded by roughly n units in the last place of the running total, which is the best behaviour a naive sum ever has. Compensated summation (Kahan, or the doubly compensated variants analysed in Higham, Accuracy and Stability of Numerical Algorithms, chapter 4) would tighten that to a constant, and would be worth having only for very long support vectors.

The validation sum is the delicate part. sum(p_i) is computed the same naive way and then compared against 1. Three separate things now depend on floating-point detail:

1. Whether the input is representable at all. Probability vectors that are exact in decimal are usually not exact in binary64. Ten copies of 0.1 do not sum to 1; neither do three copies of 1/3. A function that tested equality literally would reject most hand-written distributions, which is why a tolerance exists.
2. The tolerance width itself. It is a free parameter with observable consequences at the boundary. Widening it admits vectors that are not distributions; narrowing it rejects vectors that are. There is no principled value, only a chosen one, and Microsoft's documentation supplies none.

The accumulated interval mass and the validation sum are also computed independently, so PROB over the whole support is not guaranteed to return exactly 1 even when the validation passed. An implementation that wanted that identity would have to return the validation sum itself when the interval covers every atom, which no source suggests Excel does.

Rust code

53 prob "PROB" (3, 4) [R "x_range", R "prob_range", V "lower_limit", V "upper_limit"] |s, a, sv| {
    let xs = strict_numbers(s, a, 0, sv)?;
    let ps = strict_numbers(s, a, 1, sv)?;
    let lower = num(s, a, 2)?;
    let upper = if given(a, 3) { Some(num(s, a, 3)?) } else { None };
    out(s, k::lookup_prob_frequency_family::prob_kernel(&xs, &ps, lower, upper))
}

/// PROB's lists: every cell must hold a number (an error cell is the result, anything else
/// #VALUE!).
fn strict_numbers(site: &Site<'_>, a: &[CalcValue], index: usize, sv: &mut EvalServices<'_>) -> Step<Vec<f64>> {
    let g = grid(site, a, index, sv)?;
    let mut found = Vec::with_capacity(g.cells.len());
    for cell in &g.cells {
        match cell.core_ref() {
            CoreRef::Number(n) => found.push(n),
            CoreRef::Error(code) => return fail(code),
            _ => return fail(WorksheetErrorCode::Value),
        }
    }
    Ok(found)
}

pub(crate) fn prob_kernel(
    x_values: &[f64],
    probabilities: &[f64],
    lower: f64,
    upper: Option<f64>,
) -> Result<f64, WorksheetErrorCode> {
    if x_values.len() != probabilities.len() || x_values.is_empty() {
        return Err(WorksheetErrorCode::NA);
    }
    if probabilities
        .iter()
        .any(|p| !p.is_finite() || *p < 0.0 || *p > 1.0)
    {
        return Err(WorksheetErrorCode::Num);
    }
    let sum = probabilities.iter().sum::<f64>();
    if (sum - 1.0).abs() > PROB_SUM_TOLERANCE {
        return Err(WorksheetErrorCode::Num);
    }
    let upper = upper.unwrap_or(lower);
    if upper < lower || !lower.is_finite() || !upper.is_finite() {
        return Err(WorksheetErrorCode::Num);
    }
    let mut total = 0.0;
    for (x, p) in x_values.iter().zip(probabilities.iter()) {
        if *x >= lower && *x <= upper {
            total += *p;
        }
    }
    Ok(total)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• FREQUENCY is PROB's empirical twin. FREQUENCY counts observations falling into bins; PROB sums a given probability vector over an interval. Feed FREQUENCY's counts through a division by the sample size and you have a prob_range that PROB will accept.
• BINOM.DIST, POISSON.DIST, HYPGEOM.DIST are the named discrete distributions. Their cumulative forms answer the same question PROB answers, but from a parameterized family rather than from a tabulated one. PROB is what you use when the distribution came from data or from a model Excel does not name.
• SUMIFS computes the same sum with the interval expressed as criteria: SUMIFS(prob, x, ">="&a, x, "<="&b). What SUMIFS will not do is check that prob is a probability vector — which is PROB's entire added value and the source of both of its #NUM! conditions.
• Readers confuse PROB with PERCENTILE/QUARTILE, which invert an empirical distribution built from raw observations rather than reading a supplied probability vector.

Named variants

No named variants are recorded yet.