RAND vs RANDBETWEEN vs RANDARRAY: three flavors of random
One value or a grid, decimals or integers: the random family splits by shape and type. All three share the same gotcha — volatility — and the same cure (Paste Special → Values).
The differences at a glance
| RAND | RANDBETWEEN | RANDARRAY | |
|---|---|---|---|
| Returns | One decimal in [0,1) | One integer, both bounds inclusive | A whole spilled grid — decimals or integers |
| Custom range | Scale manually: RAND()*(max-min)+min | Built-in (min,max) | Built-in (min,max args), plus integer flag |
| Many values at once | Fill the formula down | Fill down | One formula: RANDARRAY(rows,cols,...) |
| Re-rolls on every edit | Yes | Yes | Yes — the whole grid at once |
| Availability (executed) | Universal | Universal | Excel 365, Sheets, LibreOffice 25.8+ (verified supported) |
Which should you use?
- RAND — Probabilities and continuous values — coin flips (RAND()<0.5), jittering chart points, random sort keys.
- RANDBETWEEN — Dice, test scores, sample IDs — anything that must be a whole number in a range.
- RANDARRAY — Bulk generation on modern versions — one formula fills a table, and SORTBY(range,RANDARRAY(n)) is the clean shuffle idiom.
Compatibility (from executed tests)
RAND and RANDBETWEEN execute in every version of all three apps we test; RANDARRAY is verified supported in LibreOffice 25.8 (our harness asserts its deterministic properties — shape and bounds — since the values themselves change per recalc). All three are volatile everywhere: freeze results with Paste Special → Values, or they re-roll on every edit.
Example formulas
| Coin flip | =IF(RAND()<0.5,"Heads","Tails") |
| Dice roll | =RANDBETWEEN(1,6) |
| Shuffle a list (365 / LO 25.8+) | =SORTBY(A2:A53,RANDARRAY(52)) |
Full per-version details on each function page: RAND · RANDBETWEEN · RANDARRAY.