BINOM.DIST.RANGE

What it does

BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])

BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) returns the probability that the number of successes in (n) independent Bernoulli trials falls in a closed interval:

P(s ≤ X ≤ s₂) = Σ_{k=s}^{s₂} C(n, k) · p^k · (1 − p)^{n−k}

with (n =) trials, (p =) probability_s, (s =) number_s, (s_2 =) number_s2. When number_s2 is omitted the interval collapses to a point and the function returns the single mass (b(s; n, p)) — the same quantity as BINOM.DIST(s, n, p, FALSE).

Note the argument order. trials comes first here, and number_s third. Every other binomial surface in Excel puts the success count first. Transposing the first two arguments by habit produces a call that is usually still valid and always wrong, which makes this the single most error-prone signature in the discrete family.

Domain. (n \ge 0); (0 \le p \le 1); (0 \le s \le s_2 \le n); all numeric arguments truncated to integers. Microsoft states each constraint in the argument descriptions themselves rather than in the Remarks: trials "Must be greater than or equal to 0"; probability_s "Must be greater than or equal to 0 and less than or equal to 1"; number_s "Must be greater than or equal to 0 and less than or equal to Trials"; number_s2 "Must be greater than or equal to Number_s and less than or equal to Trials".

Range. ([0, 1]), with the value 1 attained exactly when the interval covers the whole support.

Identities.

BINOM.DIST.RANGE(n, p, s, s₂) = B(s₂; n, p) − B(s−1; n, p)         difference of CDFs
BINOM.DIST.RANGE(n, p, s)     = b(s; n, p)                          single mass
BINOM.DIST.RANGE(n, p, 0, n)  = 1                                   normalisation
BINOM.DIST.RANGE(n, p, s, s₂) = BINOM.DIST.RANGE(n, 1−p, n−s₂, n−s) reflection

Arguments

There is no cumulative flag — the interval form subsumes it.

Microsoft documents "Numeric arguments are truncated to integers", which on this page covers trials, number_s and number_s2. probability_s is of course not truncated; the sentence is a family-wide phrasing rather than a literal claim about every argument.

Try it

Quirks

Result and edge cases

Returns Number.

• number_s2 omitted. A single mass. This is the reading most callers do not expect from the function's name, and it is the one that makes BINOM.DIST.RANGE(n, p, k) a synonym for BINOM.DIST(k, n, p, FALSE) — mathematically. Whether the two are the same computation is a separate question; see the numerical notes.
• number_s = 0, number_s2 = trials. The whole support; the true answer is exactly 1. The best available exactness probe.
• number_s = number_s2. A single mass, stated explicitly rather than by omission.
• trials = 0. The only admissible interval is ([0,0]), and the answer is 1.
• Wide intervals with large trials. The cost is (O(s_2 - s)) mass evaluations. There is no closed form and no early exit; a range over a million trials is a million evaluations.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native). There is no legacy alias for this function — it has no pre-2010 counterpart — so the alias questions that attach to the rest of the family do not arise here.

Errors

As documented by Microsoft on the BINOM.DIST.RANGE page:

That is unusually compact for this family: rather than enumerating conditions, the page defers to the constraints written into the argument descriptions. Expanded, the #NUM! conditions are (n < 0), (p < 0), (p > 1), (s < 0), (s > s_2), and (s_2 > n).

How to implement

Numerical notes

Why the function exists. Given the two cumulative values, the interval probability is their difference. When both are near 1 — an interval in the upper tail — that difference is catastrophic cancellation: two numbers agreeing in the leading digits are subtracted and the result is built entirely from the digits that were least reliable. Summing the interval's own masses instead is a sum of positive quantities, and a sum of positive quantities has no cancellation at all. Its relative error is bounded by the ordinary summation bound ((\gamma_{m-1}) for (m) terms; Higham, Accuracy and Stability of Numerical Algorithms, chapter 4), which for realistic interval widths is negligible.

That is the whole numerical argument for the surface, and it is a good one. It is also why an implementation that quietly computes this function as a difference of CDFs would be wrong precisely in the regime the function was added for.

• For trials at or below a fixed small threshold, and with (p) strictly between 0 and 1, it sums masses computed by a direct combinatoric form — building (C(n,k)) by a running product of ratios and multiplying by integer powers of (p) and (q).
• Otherwise it sums masses computed by the general saddle-point path described on BINOM.DIST.

Cost. (O(s_2 - s)) mass evaluations with no shortcut. For a wide interval the incomplete-beta route is asymptotically better, and for an interval covering most of the support the complement (1 minus the two excluded tails) is better still — at the cost of reintroducing exactly the cancellation this function avoids. There is no universally right choice, which is itself worth stating.

Rust code

72 binom_dist_range "BINOM.DIST.RANGE" (3, 4) [V "trials", V "probability_s", V "number_s", V "number_s2"] |s, a, sv| {
    let [t, p, n] = nums::<3>(s, a)?;
    let n2 = if given(a, 3) { Some(num(s, a, 3)?) } else { None };
    out(s, dd::binom_dist_range_kernel(t, p, n, n2))
}

/// `BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])`.
pub fn binom_dist_range_kernel(trials: f64, p: f64, number_s: f64, number_s2: Option<f64>) -> R {
    let n = trials.trunc();
    let s = number_s.trunc();
    let s2 = number_s2.map_or(s, f64::trunc);
    if n < 0.0 || !(0.0..=1.0).contains(&p) || s < 0.0 || s > n || s2 < s || s2 > n {
        return Err(NUM);
    }
    let mass = |i: f64| {
        if p == 0.0 {
            if i == 0.0 { 1.0 } else { 0.0 }
        } else if p == 1.0 {
            if i == n { 1.0 } else { 0.0 }
        } else {
            binom_mass(i, n, p, 1.0 - p)
        }
    };
    let mut sum = 0.0;
    let mut i = s;
    while i <= s2 {
        sum += mass(i);
        i += 1.0;
    }
    finite(sum.min(1.0))
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• BINOM.INV — the quantile of the same distribution.
• BINOMDIST / CRITBINOM — the legacy names for the mass/cumulative and quantile forms. BINOM.DIST.RANGE has no legacy counterpart; it was introduced with the dotted-name generation, which is why its argument order follows no older convention.
• BETA.DIST — the incomplete-beta identity gives a closed-form route to the same interval probability as a difference of two (I_p) values, which for very wide intervals is (O(1)) rather than (O(s_2-s)).
• POISSON.DIST, NORM.DIST — the limiting approximations, and the usual (inaccurate) substitutes for a wide-interval binomial probability.
• Confused with: BINOM.DIST with the arguments in the same order. They are not in the same order. This is the relationship most worth remembering.

Named variants

No named variants are recorded yet.