Return a random integer number between 2 values
=RANDBETWEEN(bottom,top)
RANDBETWEEN is useful for generating random integer numbers that recalculate every time a sheet is opened or changed or F9 is pressed.
RANDBETWEEN defines the range of integers ('bottom' and 'top') from which the random number should be generated.
RANDBETWEEN and numbers.
In the examples below, the RANDBETWEEN function is used to generate volatile and non-volatile random integers, in single cells and arrays.
Use the RANDBETWEEN function to generate random integers between 2 values.
Syntax
=RANDBETWEEN(bottom,top)
The RANDBETWEEN function has 2 arguments.
- bottom. Required. The smallest integer the RANDBETWEEN function will return.
- top. Required. The largest integer the RANDBETWEEN function will return.
Try it now!
- Enter your formulas into the grey cells.
Scope
RANDBETWEEN defines the range of integers ('bottom' and 'top') from which the random number should be generated.
|
=RANDBETWEEN(5,25) || result is a random integer greater than or equal to 5 and less than or equal to 25. |
|
=RANDBETWEEN(-25,-5) || result is a random integer greater than or equal to -25 and less than or equal to -5. Note the order of the negatives. If the formula is mistakenly written as =RANDBETWEEN(-5,-25), #NUM! will be returned. |
|
=RANDBETWEEN(5,25) then press F9 || result is a random integer greater than or equal to 5 and less than or equal to 25 that does not change when the workbook is opened or changed. |
Caveats
- RANDBETWEEN generates random integers. For decimal values, use the RAND function.
- RANDBETWEEN is a volatile function that will change every time a workbook is opened or changed or the F9 key is pressed.
- To create a single non-volatile random number, enter the =RANDBETWEEN(bottom,top) function into the formula bar then press F9 to convert the formula into its result. Alternatively, copy and paste special values the result into the desired cell.
- To create an array of random numbers, select the desired range, then enter =RANDBETWEEN(bottom,top) followed by CTRL + ENTER. This generates the formula {=RANDBETWEEN(bottom,top)}.
- The RANDARRAY (Excel 365) function can also be used to generate random values between 2 values.
Common Issues
- #NUM!: the bottom and top values are in the incorrect order.
Related Functions
- RAND: return a random decimal value between 0 and 1.
- RANDARRAY: (Excel 365) return an array of random decimal or integer numbers between specified values.
Functions Category