RANDBETWEEN Function (LibreOffice Calc)
The RANDBETWEEN function in LibreOffice Calc returns a random integer between two specified values. It recalculates on every sheet update and is essential for simulations, sampling, testing, and randomized models.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the RANDBETWEEN Function Does â–¾
- Returns a random integer between two bounds
- Recalculates on every sheet update
- Useful for simulations, sampling, and randomized models
- Works with any integer range
- Complements the RAND function
It is designed to be simple, flexible, and universally compatible.
Syntax â–¾
RANDBETWEEN(bottom; top)
Arguments
-
bottom:
The lowest integer to return. -
top:
The highest integer to return.
Basic Examples â–¾
Random integer between 1 and 10
=RANDBETWEEN(1; 10)
Random integer between 0 and 100
=RANDBETWEEN(0; 100)
Random integer between two cells
=RANDBETWEEN(A1; B1)
Random dice roll (1–6)
=RANDBETWEEN(1; 6)
Advanced Examples â–¾
Random even number between 2 and 20
=RANDBETWEEN(1; 10) * 2
Random odd number between 1 and 19
=RANDBETWEEN(0; 9) * 2 + 1
Random selection from a list
=INDEX(A1:A10; RANDBETWEEN(1; COUNTA(A1:A10)))
Random date between two dates
=RANDBETWEEN(A1; B1)
Where A1 and B1 contain date serial numbers.
Random time between two times
=RANDBETWEEN(A1 * 86400; B1 * 86400) / 86400
Random Boolean (TRUE/FALSE)
=RANDBETWEEN(0; 1) = 1
Random sample without replacement (manual method)
- Add a helper column with
=RAND() - Sort by the helper column
- Take the top N rows
Random normal distribution (approximate)
=NORMINV(RAND(); mean; stdev)
(Excel only; Calc users use Box–Muller with RAND.)
Common Errors and Fixes â–¾
RANDBETWEEN returns Err:502
Occurs when:
- bottom > top
- arguments are non-numeric
- arguments are text
RANDBETWEEN recalculates unexpectedly
Cause:
- Any sheet recalculation triggers new values
- Sorting, editing, or pressing F9 updates results
Fix:
Copy → Paste Special → Values to freeze results.
RANDBETWEEN returns the same number repeatedly
Possible causes:
- AutoCalculate disabled
- Sheet not recalculating
Fix:
Enable: Tools → Options → LibreOffice Calc → Calculate → AutoCalculate.
Best Practices â–¾
- Use RANDBETWEEN for integer ranges
- Use RAND for decimal ranges
- Freeze results with Paste Special → Values
- Use RANDBETWEEN with INDEX for random selection
- Use RANDBETWEEN for sampling, testing, and simulations
Related Patterns and Alternatives â–¾
- Use RAND for decimal random values
- Use INT(RAND() * (b - a + 1)) + a for manual integer ranges
- Use INDEX + RANDBETWEEN for random selection
- Use RAND() < p for probability-based logic
By mastering RANDBETWEEN and its combinations with other math functions, you can build powerful, flexible randomization and simulation models in LibreOffice Calc.