RAND

What it does

RAND()

RAND() returns a pseudo-random number in the half-open interval [0, 1) — at least zero and strictly less than one — intended to be uniformly distributed, and recomputed on every recalculation.

Microsoft's Excel JavaScript API reference states the contract in one sentence: "Returns a random number greater than or equal to 0 and less than 1, evenly distributed (changes on recalculation)." Both endpoints matter. The lower end is attainable and the upper end is not, which is what makes RAND()*(b-a)+a a correct sampler for [a, b) and 1/RAND() a formula that can divide by zero.

Arguments

None. Arity is exactly zero and the parentheses are mandatory syntax.

The absence of a seed argument is the single most consequential design decision on this page: there is no worksheet-level way to reproduce a draw, and therefore no worksheet-level way to make a model deterministic. Workbooks that need reproducibility freeze values (paste-as-values) or move the generator outside Excel.

Try it

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

Quirks

Result and edge cases

Returns Number in [0, 1).

• Volatility. VolatileFull means the cell recomputes on every recalculation of the workbook, not only when a precedent changes. A sheet full of RAND() re-randomises when you edit an unrelated cell, press F9, or open the file. This is a property of the engine's dependency handling, not of the function's mathematics, and it is why a RAND() column cannot be used as a stable key.
• Thread serialisation. HostSerialized records that the surface cannot be evaluated freely in parallel: a shared stream has to be advanced under a lock, or two threads get the same draw. Any implementation that parallelises recalculation has to decide whether the sequence of draws is part of the observable behaviour. If it is, parallel evaluation changes results; if it is not, nothing is reproducible anyway.
• Zero is attainable in principle. The documented interval includes 0, so a formula that divides by RAND() has a genuine — if astronomically unlikely, and generator-dependent — failure mode. Whether any real implementation ever emits exactly 0 depends on how the bits are assembled; see the numerical notes.

Errors

No documented error condition. Nothing in the argument list can fail, because there is no argument list.

How to implement

Numerical notes

Resolution. A generator producing a [0,1) double can offer anywhere from 2⁻³² to 2⁻⁵³ spacing. Two constructions dominate: k/2⁵³ with k a 53-bit integer (uniform on a lattice, never returns exactly 1, can return exactly 0), and (k+½)/2⁵³ (never returns either endpoint). A generator built by dividing a 32-bit integer by 2³² gives only about 4×10⁹ distinct values, which is visible as ties in any sample of a few hundred thousand rows — the birthday bound bites around 65,536 draws. If a workbook's Monte Carlo shows duplicate draws, this is why.

Period and equidistribution matter more than "randomness". A combined multiplicative congruential generator in the Wichmann–Hill family (AS 183) has a period around 7×10¹² and fails modern spectral tests; a Mersenne Twister has period 2¹⁹⁹³⁷−1 and 623-dimensional equidistribution but is not cryptographic and fails linear-complexity tests; a modern counter-based or xoshiro-family generator does better in less state. For a spreadsheet, the practical questions are (a) how many draws before the sequence repeats, (b) whether consecutive draws are independent when used as coordinates of a point — the classic lattice failure that makes a pair of RAND() cells trace visible planes in a scatter plot — and (c) whether the low bits are as good as the high bits. L'Ecuyer & Simard's TestU01 is the standard instrument for all three.

Streams and parallelism. Once a generator is shared across cells, the assignment of draws to cells becomes part of the answer. The two disciplined solutions are a single serialised stream (what HostSerialized describes) and per-cell counter-based generation, where the draw is a keyed function of the cell address and a workbook counter — reproducible, parallel-safe, and compatible with nothing that already exists.

On Excel's actual generator, this page says nothing. Microsoft has characterised it in different terms across versions, and external authors — McCullough & Wilson most persistently — have published critiques of specific releases. The Handbook has not tested any Excel build's generator and does not restate anyone's account of it. What can be said without evidence is only the contract: half-open, uniform by declaration, volatile.

Rust code

1 rand "RAND" (0, 0) [] |s, a, sv| {
    let unit = draw(s, sv)?;
    s.number(unit)
}

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

• RANDBETWEEN — the integer sampler, built from the same stream, with an inclusive upper bound where RAND has an exclusive one. The endpoint difference is the most common source of off-by-one sampling errors in spreadsheets.
• RANDARRAY — the dynamic-array generalisation, which subsumes both: RANDARRAY() with no arguments is RAND(), and RANDARRAY(1,1,a,b,TRUE) is RANDBETWEEN(a,b).
• RAND()*(b-a)+a — the documented idiom for a uniform draw on [a, b). It inherits RAND's half-open interval, and it loses resolution when a and b are far apart in magnitude.
• INDEX(range, RANDBETWEEN(1, ROWS(range))) — sampling with replacement. There is no built-in sampler without replacement; SORTBY(range, RANDARRAY(ROWS(range))) is the modern idiom, and it is a permutation only if the draws are distinct.
• NORM.INV(RAND(), μ, σ) — the inverse-transform route to a normal deviate, and the place where the quality of RAND's low-order bits and the accuracy of the inverse CDF in the tails both stop being academic.

Named variants

No named variants are recorded yet.