This Excel tutorial explains how to use Excel RANDBETWEEN Function and Excel RAND Function to generate random number and explain the difference.
Excel RAND RANDBETWEEN Function – generate random number
Before Excel 2007, Excel uses RAND Function to generate a random number that is greater than zero and smaller than 1.
Since Excel 2007, a new Function RANDBETWEEN was added, it is used to generate a random integer between two desired integers.
Note that RANDBETWEEN and RAND Functions are worksheet Functions, VBA only supports RND Function, which generates a decimal number between 0 to 1.
However, you can still access RANDBETWEEN Function in VBA by typing
Excel RAND Function – generate random number between 0 and 1
Excel RAND Function has no parameter, it generates a random number greater than zero and smaller than 1 (both exclusive). To use the Function in Excel worksheet,simply type
To generate a number with desired upperbound and lowerbound, type
Syntax of Excel RANDBETWEEN Function – generate random number
|bottom||The minimum integer of the random number, this number is inclusive in the random range|
|top||The maximum integer of the random number, this number is inclusive in the random range. #Num Error will return if bottom is larger than top.|
If you cannot use this function, enable Analysis TooPak under Developer tab in menu as below.
Read the below article if you cannot find the Developer tab.
Example of Excel RANDBETWEEN Function – generate random number
You should note carefully that by default, “Workbook Calculation” is “Automatic”, which means each time you edit any Cell (Worksheet Change Event in terms of VBA), a new Random number is regenerate.
For example, type =RANDBETWEEN(1,100) in Cell A1, it generates 31, but as you edit Cell A2, A1 will regenerate another random number, say, 70.
To solve this issue, you can go to Excel Options and change the Workbook Calculation to “Manual”. Now the random number will only regenerate when you edit the formula, but so will other non RANDBETWEEN related formula.