The ability to generate random numbers in Excel is essential, particularly in the field of data analysis. Random numbers are frequently used to create pseudo-datasets or to fill in missing values based on the minimum and maximum values of a column. In this article, we will explore how to generate random numbers in Excel, whether in a single cell or as an array spanning multiple rows and columns.
The main functions we focus on are, RAND, RANDBETWEEN, and RANDARRAY. We’ll also uncover the hidden power of the F9 key. Let’s dive in!
RAND Function
RAND()When the above function is executed on a cell, it will generate a real number greater than or equal to zero (0) and less than one (1). This value will be regenerated everytime the worksheet is calculated.
Generating Real Numbers
The RAND function returns real numbers between 0 and 1. By multiplying it’s result by a specific number, we can scale the output to a desired range of 0 and that specific number. For example:
RAND()*2Return a real number that is always less than 2 but greter or equal to 0. Like wise, to generate a random real number between 0 and 10, we do it a s follows:
RAND()*10Generating Ingegers
To generate integer value, we wrap the RAND function with in INT function. What the INT function does is taking a real number and extract only the integer value.
INT(RAND())In the above case, we get (0) every single time because RAND function never includes (1). To fix this, we need to multiply the RAND function by a desired range size. For example, to generate an integer number between 0 and 1 (inclusive), we multiply the function by 2.
INT(RAND()*2)Why 2? Multiplying by 2 ensures the result is either 0 or 1, as RAND will never reach 1. Similarly, to generate a number between 0 and 9 (inclusive), we multiply the function by 10 as follows:
INT(RAND()*10)To include 10, we multiply the function by 11. This approach scales the output range between 0 and the desired range value while ensuring the result is always an integer.
Including the Last Value
The method above may seem a bit complex, but there’s an easier alternative that is using the ROUND function instead of INT. The ROUND function rounds the real number generated by RAND to the nearest integer. For example, to generate an integer between 0 and 1 (inclusive), we can use:
ROUND(RAND(), 0)The 0 in the ROUND function ensures that the result is always an integer with no decimal places. For more details about the ROUND function, visit this Microsoft Suport page .
ROUND(RAND() * 10, 0)The above code generates a random integer between 0 and 10.
Number between a and b
So far, we have seen how to generate random values between 0 and a specified number. But what if we want to generate a random value between, say, 10 and 25 (inclusive)? Here’s how we can do it:
ROUND(10 + RAND() * 15, 0)Similarly, to generate a number between 1 and 5, we can do as follows:
ROUND(1 + RAND() * 4, 0)If you observe the pattern, we shift the starting point from 0 to a (the minimum value) by adding a to the output of the RAND function. To adjust the range, we multiply RAND by the difference (b - a). This ensures the random value always falls between a (minimum) and b (maximum).
RANDBETWEEN Function
This function works similar to the RAND function generating a number between a and b, but in more simpler way. To generate a number between 0 and 1 (inclusive), we can use the following method:
RANDBETWEEN(0, 1)To generate a number between 10 and 25, we do as follows:
RANDBETWEEN(10, 25)Notice that we didn’t use the INT or ROUND functions with RANDBETWEEN? That’s because the RANDBETWEEN function inherently returns an integer value on its own.
RANDARRAY Function
In the above sections, we generated random values for a single cell. But what if we want to generate random numbers across multiple rows and columns? The only option so far is to copy and paste the formula into each desired cell, which isn’t very efficient. This is where the RANDARRAY function comes in, offering a more intuitive solution. The syntax is as follows:
RANDARRAY(NO_OF_ROWS, NO_OF_COLUMNS, [MIN_VALUE], [MAX_VALUE], [IS_IT_INTEGER])Using the syntax above, we can generate random real numbers between 0 and 1 (excluding) for a range of 5 rows and 3 columns. Here is how we can do it:
RANDARRAY(5, 3)Notice that we didn’t provide the MIN_VALUE, MAX_VALUE, and IS_IT_INTEGER arguments. That’s because they are optional and they have default value of 0, 1, and FALSE, respectively.
To generate an integer values between 1 and 8 across 3 rows and 5 columns, we can use the following method:
RANDARRAY(3, 5, 1, 8, TRUE)One important note here is that for integer values, the MAX_VALUE is included in the result, whereas for real numbers, the MAX_VALUE is not included.
Locking Values
For all the methods mentioned above, the values will be regenerated every time the worksheet is recalculated. While it can be exciting to get new values with each calculation, there are times when you might want the values to remain static. Pressing F9 after entering the functions and their arguments ensures the values to stay fixed, regardless of whether the worksheet is recalculated or not.