Excel has several functions for generating random numbers (data), such as RAND(), RANDBETWEEN(), and RANDARRAY(). Here are some examples of how to use them.
Generate between range
To generate a random number between a range, use the RANDBETWEEN formula. This formula produces only whole numbers and does not generate decimal numbers. All you need to do is to specify the lower and upper limit values.
In the example below, we specified 0 as the lower value and 1 as the upper value. Since it does not generate decimal numbers, it will give either 0 or 1.
=RANDBETWEEN (0, 1)
The following formula generates a random number between 100 and 500: =RANDBETWEEN (100, 500)
You can also specify negative numbers as the lower and upper values. In the following formula, the random number range is between -30 and -5:
=RANDBETWEEN (-30, -5)
Random number between 0 and 1
You can generate a uniformly distributed random number between 0 and 1 using RAND function.
The formula is used as follows. Since it does not take any parameter, simply type the function =RAND() and press the enter key.
Random number between x and y
Let X and Y be two numbers and X<Y. To generate a random number between these two numbers, we can modify the formula as follows.
=RAND() * (Y – X) + X
For instance, you can write the formula to generate between -1 and 1 like below.
=RAND() * (1 – (-1)) + 1)
Random data generator
There is another function that can be used a random number generator. It is called RANDARRAY function. You can generate a random data that consists whole numbers or decimal numbers by specifying the number of rows and columns.
For example, the following formula produces a 7 (row) by 5 (column) array of random numbers. The numbers are between 10 and 100 and are all integers.
=RANDARRAY (7, 5, 10, 100, TRUE)
If you want decimal numbers, change the last parameter to “FALSE”.
=RANDARRAY (7, 5, 10, 100, FALSE)
Important Note: If you make changes on the page, you will see that different numbers are generated every time you refresh the formula. Press F2 to edit the cell, then press the F9 key. It converts the cell that contains formula to a value.