RANDBETWEEN

What it does

RANDBETWEEN(bottom, top)

RANDBETWEEN(bottom, top) returns a pseudo-random integer between bottom and top, inclusive at both ends, redrawn on every recalculation.

P(X = k) = 1 / (top − bottom + 1)    for each integer k with bottom ≤ k ≤ top

Microsoft's VBA reference states the endpoints as inclusive without using the word: bottom is "the smallest integer RandBetween will return" and top is "the largest integer RandBetween will return". The same page states that "a new random integer number is returned every time the worksheet is calculated".

The inclusive upper bound is the fact to carry away. RAND returns a value in [0, 1) — upper bound excluded. RANDBETWEEN includes both bounds. So RANDBETWEEN(1, N) picks one of N items, while INT(RAND()*N)+1 picks one of N items by a different route with a different failure mode, and RANDBETWEEN(0, N) has N+1 outcomes, not N. Off-by-one errors in spreadsheet sampling almost always trace back to mixing the two conventions.

Arguments

Both are numeric slots under ordinary to-number coercion — see Coercion and lifting. Microsoft's page types both as Variant, consistent with text and logicals converting.

Try it

OneCalc does not supply a random-number source in this preview.

Quirks

Result and edge cases

Returns Number, always integral.

• bottom = top returns that value with probability 1 — a degenerate but legal draw.
• Non-integral bounds narrow inward, as above; if ⌈bottom⌉ > ⌊top⌋ — for example RANDBETWEEN(1.2, 1.8), where no integer lies in the interval — the same refusal applies. This is a case a reader is unlikely to anticipate: an interval that plainly contains numbers contains no integers, and the function errors.
• Volatility. VolatileFull, PseudoRandom, HostSerialized, RandomProvider — the whole discussion on RAND applies unchanged. The value is not stable; a RANDBETWEEN cell cannot be used as a key.
• Very wide ranges. When top − bottom + 1 approaches or exceeds 2⁵³, the arithmetic that produces the draw stops being exact; see the numerical notes.

How to implement

Numerical notes

Uniformity is approximate, and the error is exactly N/2⁵³. The construction lo + ⌊u·N⌋, with u uniform on a lattice of 2⁵³ values and N = hi − lo + 1, distributes 2⁵³ lattice points among N buckets. If N is not a power of two the buckets cannot receive equal counts, and the largest and smallest bucket probabilities differ by a relative amount of about N/2⁵³. For RANDBETWEEN(1,6) that is around 10⁻¹⁵ — undetectable in any experiment that will ever be run. For N above 2⁴⁰ it becomes measurable, and for N near 2⁵³ the distribution is visibly lumpy.

The unbiased alternatives are well known: rejection sampling (redraw when u falls in the incomplete final block) and Lemire's multiply-shift method, which achieves the same at the cost of one multiplication and a rarely taken branch. Both are strictly better mathematics. Neither may be substituted into an implementation that is targeting compatibility, because each changes which draw a given stream position produces — so this is a case where natural-best and excel-bitexact must genuinely differ; see About implementation options.

A wide range does not give a wide sample. Because the source lattice has 2⁵³ points, a RANDBETWEEN over a range wider than the generator's resolution cannot reach every integer in the range — it reaches at most as many distinct values as the generator has. If the underlying stream is 32-bit, that ceiling is around four billion regardless of the bounds requested. Anyone using RANDBETWEEN(1, 10^15) as an identifier generator should know this.

Rust code

2 randbetween "RANDBETWEEN" (2, 2) [V "bottom", V "top"] |s, a, sv| {
    // Excel rounds bottom up and top down to integers.
    let lo = num(s, a, 0)?.ceil();
    let hi = num(s, a, 1)?.floor();
    if lo > hi {
        return fail(WorksheetErrorCode::Num);
    }
    let unit = draw(s, sv)?;
    let range = hi - lo + 1.0;
    s.number((lo + (unit * range).floor()).min(hi))
}

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

• RAND — the continuous source. RANDBETWEEN is built from it, and inverts its endpoint convention.
• INDEX(range, RANDBETWEEN(1, ROWS(range))) — sampling with replacement, the idiom this function mostly exists to serve.
• CRITBINOM/BINOM.INV — the other route to a discrete draw, via an inverse CDF. Different distribution, same dependence on the quality of the underlying uniform.

Named variants

No named variants are recorded yet.