RANDBETWEEN
Return a random integer between two bounds, inclusive. A new value every time the sheet recalculates.
Syntax
RANDBETWEEN(bottom, top)| Argument | Required | Description |
|---|---|---|
bottom | Required | The smallest value that can be returned. Fractions are rounded up. |
top | Required | The largest value that can be returned. Fractions are rounded down. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=AND(RANDBETWEEN(1,6)>=1,RANDBETWEEN(1,6)<=6) | TRUE | A die roll — both ends are included. |
=RANDBETWEEN(4,4) | 4 | When the bounds meet there is only one answer. |
=RANDBETWEEN(1.5,2.5) | 2 | The bounds are rounded inwards, so only 2 fits. |
=RANDBETWEEN(6,1) | #NUM! | The bottom must not be above the top. |
Test data and sampling
RANDBETWEEN fills a column with plausible whole numbers — quantities, ages,
IDs — for trying out a formula before real data arrives. Paired with
INDEX it picks a random row:
=INDEX(A2:A100,RANDBETWEEN(1,99))Both ends included
RANDBETWEEN(1,6) can return 1 and can return 6, each as often as 3. Fractional
bounds are rounded inwards — the bottom up, the top down — so the result is
always inside the range you gave.
Like RAND, it recalculates on every edit; paste as values
to keep a draw.
Excel compatibility
Matches Excel, including rounding the bounds inwards and #NUM! when they cross.