RANDARRAY

What it does

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY([rows], [columns], [min], [max], [whole_number]) returns an array of random values. The defaults are one row, one column, minimum 0, maximum 1 and decimal results. Set whole_number to TRUE to request integers.

=RANDARRAY(2,3) requests two rows and three columns. =RANDARRAY(2,3,1,6,TRUE) requests a grid of whole numbers in the stated range. The array changes when Excel recalculates it; copy and paste values if a particular sample must be retained.

Try it

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

The example is editable, but OneCalc does not supply a random stream in this preview. Calculate reports that limit explicitly. It does not substitute a fixed sample or a different generator.

Quirks

An array needs enough empty worksheet cells to spill. A blocked spill and invalid function arguments are separate problems. A formula used as a random sort key can change on recalculation, so do not treat a recalculated random array as a persistent identifier.

Microsoft says the minimum must be less than the maximum and documents #VALUE! otherwise. The OneCalc kernel accepts equal endpoints and returns that value. There is no measured Excel comparison here resolving the equal-endpoint case; the two accounts must remain distinct.

The continuous distribution, integer endpoint handling and treatment of non-integral dimensions need separate checks. A real-number interval formula does not by itself prove which binary64 endpoints are attainable. Drawing successive values from a pseudorandom stream also does not prove statistical independence.

How to implement

Validate dimensions before allocation and check their product for overflow. Make the source of randomness an explicit service so that recalculation, stream ownership and replay are under the caller's control. Fill one two-dimensional result using a declared traversal order.

For a chosen continuous sampler, transforming a unit draw with min + u*(max-min) is a common starting point, but the arithmetic can overflow or round to an endpoint. For uniform bounded integers, rejection sampling avoids the bias that a naive modulo or floor construction can introduce when the source range is not a multiple of the target range.

Those are choices for a new sampler. Reproducing Excel additionally requires observations of its generator, seed or stream state, integer mapping and draw order. Matching the requested range and array shape does not reproduce Excel's random sequence. Keep missing randomness a refusal rather than silently returning cached or deterministic values.

Rust code

3 randarray "RANDARRAY" (0, 5) [V "rows", V "columns", V "min", V "max", V "whole_number"] |s, a, sv| {
    let rows = if given(a, 0) { num(s, a, 0)?.trunc() } else { 1.0 };
    let cols = if given(a, 1) { num(s, a, 1)?.trunc() } else { 1.0 };
    let min = if given(a, 2) { num(s, a, 2)? } else { 0.0 };
    let max = if given(a, 3) { num(s, a, 3)? } else { 1.0 };
    let whole = if given(a, 4) { opt_flag(s, a, 4, false)? } else { false };
    if rows < 1.0 || cols < 1.0 {
        return fail(WorksheetErrorCode::Value);
    }
    if rows * cols > RANDARRAY_LIMIT {
        return Err(s.not_modelled(None));
    }
    let (lo, hi) = if whole { (min.ceil(), max.floor()) } else { (min, max) };
    if hi < lo {
        return fail(WorksheetErrorCode::Value);
    }
    let mut cells = Vec::with_capacity(rows as usize);
    for _ in 0..rows as usize {
        let mut row = Vec::with_capacity(cols as usize);
        for _ in 0..cols as usize {
            let unit = draw(s, sv)?;
            row.push(if whole {
                let count = hi - lo + 1.0;
                lo + (unit * count).floor().min(count - 1.0)
            } else if hi == lo {
                lo
            } else {
                lo + unit * (hi - lo)
            });
        }
        cells.push(row);
    }
    out_matrix(s, &cells)
}

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

Version differences

Microsoft lists RANDARRAY for Microsoft 365 and Excel 2021/2024, including the listed mobile editions. No comparison of generated streams or endpoint rules between named builds is recorded here. Product availability is separate from the random algorithm used by a build.

Alternatives

RAND returns one unit-interval value. RANDBETWEEN requests one bounded integer. SEQUENCE creates a deterministic grid. A sampler with an explicit seed is more suitable for reproducible experiments, but its stream need not be Excel's.

Named variants

No named variants are recorded yet.