The RAND and RANDBETWEEN functions in Excel
RAND has no arguments. It returns a decimal greater than or equal to 0 and less than 1. It is volatile: any change on the sheet, or F9, gives a new value.
RANDBETWEEN(bottom, top) returns a whole number from bottom to top, including both ends. The examples below use equal bounds so the result is stable and can be checked.
Syntax
=RAND()
| Argument | What it means |
|---|
Examples
1. A fixed integer from RANDBETWEEN
| A | B | |
|---|---|---|
| 1 | Roll | Result |
| 2 | 5 |
Bottom and top are both 5, so the only possible result is 5. In real use you would write =RANDBETWEEN(1,6) for a dice roll.
2. Another fixed bound
| A | B | |
|---|---|---|
| 1 | Code | 10 |
Equal bounds pin the result so the example can be checked. Widen the bounds when you want a real random integer.
3. A whole number from a RAND-style scale
| A | B | |
|---|---|---|
| 1 | Ones | 1 |
RAND itself cannot be checked here because every recalculation changes it. =INT(RAND()*10) would give 0 to 9; use RANDBETWEEN when you need integers.
Common mistakes
Expecting RAND() to stay put. It recalculates whenever the workbook calculates.
Fix: Copy the cell and Paste Values when you need a snapshot. F9 forces a new draw while you are testing.
Thinking RANDBETWEEN excludes the top. Both ends are included:
=RANDBETWEEN(1,2)can be 1 or 2.Fix: Count the integers in the closed interval. From 1 to 6 there are six faces, not five.
Frequently asked questions
Can RAND return 1?
No. The result is 0 ≤ x < 1. RANDBETWEEN is the function that includes both of its bounds.
How do I get a random decimal in a range, say 2 to 5?
=2+RAND()*3 scales RAND into that interval (2 inclusive, 5 exclusive). For integers, use RANDBETWEEN.
Related functions
- ROUNDROUND rounds a number to a set number of digits; ROUNDUP and ROUNDDOWN always round away from or toward zero.
- MODMOD returns the remainder after division.
More on RAND and random numbers
RAND returns a random decimal number from 0 up to, but not including, 1. It needs no arguments and gives a new value whenever the sheet recalculates, which makes it useful for simulations, sampling and shuffling lists.
Why the numbers keep changing
RAND is a volatile function. Excel recalculates it every time anything in the workbook changes, and again when you press F9 or open the file. That is the whole point for a dice roll, and a nuisance when you want a fixed result.
To freeze the numbers, copy the cells and use Paste Special, Values. The formulas are replaced by the numbers they showed at that moment, and they stop changing.
Turning 0 to 1 into the range you need
Multiply and add to stretch the output. A random decimal between 10 and 20, and a whole-number dice roll from 1 to 6:
=10+RAND()*10
=ROUNDDOWN(RAND()*6,0)+1For whole numbers there is a shortcut: RANDBETWEEN takes a lowest and a highest value and includes both ends, so =RANDBETWEEN(1,6) gives the same dice roll.
Shuffling and sampling a list
Put a RAND formula next to each item and use that column as a sort key. Because the values are random, the order after sorting is random too. To pick a random sample of 10 rows, rank the random numbers and keep the rows ranked 1 to 10:
=RANK.EQ(B2,$B$2:$B$101)Paste the random column as values before you sort, otherwise the sort itself triggers a recalculation and the order changes under you.
Cautions
- Every RAND cell is recalculated on each edit, so thousands of them can slow a large workbook.
- RAND is fine for games and sampling, but it is not suitable for security or anything that must be reproducible. Excel offers no way to set a seed.
- Duplicates are possible in theory when you generate many numbers, although with decimals they are very rare. With RANDBETWEEN on a small range, expect repeats.