Excel RAND RANDBETWEEN Function to generate random number

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

WorksheetFunction.RandBetween(...)

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

=RAND()

To generate a number with desired upperbound and lowerbound, type

=RAND()*(upperbound-lowerbound)+lowerbound

Syntax of Excel RANDBETWEEN Function – generate random number

RANDBETWEEN(bottom,top)

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.

Add Excel Developer Tab

Example of Excel RANDBETWEEN Function – generate random number

Formula	Return
=RANDBETWEEN(1,100)	31
=RANDBETWEEN(100,1000)	661

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.

Outbound References

http://www.techonthenet.com/excel/formulas/randbetween.php