CRITBINOM

What it does

CRITBINOM(trials, probability_s, alpha)

CRITBINOM(trials, probability_s, alpha) returns the smallest success count whose cumulative binomial probability is at least alpha. Trials are independent and share a success probability. CRITBINOM is the compatibility name.

Try it

=CRITBINOM(4,0.5,0.6) returns 2. The cumulative probabilities through zero, one and two successes are 1/16, 5/16 and 11/16; two is the first count to reach 0.6.

Quirks

The trial count is truncated toward zero. The current helper then requires n>=0, 0<p<1 and 0<alpha<1; probabilities at either endpoint are rejected with #NUM!. Nonnumeric arguments give #VALUE!. Unlike BINOM.DIST, this inverse does not admit degenerate endpoint probabilities.

At an exact cumulative boundary, “at least” means >=, not >. A count can change because the accumulated floating-point CDF falls on a different side of alpha, even when two mass formulas agree to many decimal places. The returned result is an integer count, not an interpolated fraction of a trial.

How to implement

The current candidate is a sequential search:

1. Truncate and validate the trial count and the two open-interval probabilities.
2. Form the stored binary64 complement q=1-p; start sum=0 and k=0.
3. While k<n, obtain the current shared binomial mass at k, add it to sum, and return k if sum>=alpha.
4. Otherwise increment k. If no earlier count reaches the criterion, return n. For admitted n=0, this final branch returns zero.

For four trials with p=0.5, the mathematical masses are 1,4,6,4,1 divided by sixteen. The cumulative values at counts zero, one and two are 1/16, 5/16 and 11/16; alpha=0.6 first qualifies at two. At criterion 5/16, the inclusive mathematical comparison first qualifies at one.

A large-n implementation can bracket a count and binary-search a stable CDF, but that is a different algorithm. It needs checks around adjacent counts and exact CDF boundaries; stopping when the numerical probability is merely close to alpha can return the wrong integer. The current sequential routine retains recorded mismatches, so neither this search outline nor a generic inverse library is a solved whole-domain Excel quantile recipe.

Rust code

74 critbinom "CRITBINOM" (3, 3) [V "trials", V "probability_s", V "alpha"] |s, a, sv| {
    let [t, p, alpha] = nums::<3>(s, a)?;
    out(s, dd::binom_inv_kernel(t, p, alpha))
}

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

BINOM.DIST maps a count to a probability; BINOM.INV supplies the other spelling.

Named variants

No named variants are recorded yet.