Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.
In the image below in cell D2 the RANDARRAY function is producing six (cell B4) random whole numbers between 1 (cell B2) and 45 (cell B3). The problem in this case is that 30 has been repeated.
The formula in cell D2 is.
=RANDARRAY(B4,1,B2,B3,1)The first 1 in the function defines one column of random numbers.
The final 1 in the function specifies returning whole numbers.
This formula spills down because it is a dynamic array. If you are not familiar with dynamic arrays check out the blog post below.
Note each time Excel calculates the random numbers generated will change.
Unique random whole numbers
To generate unique random whole numbers, we need to combine a few functions. I will create the final formula over a few steps to explain how the functions work together.
Let’s start with a list of 45 sequential numbers in cell F2. The formula is.
=SEQUENCE(B3,1,B2)See image below.
The list extends below the screen shot.
In the cell next to it we can create 45 random numbers. These aren’t whole numbers.
The formula in cell G2 is.
=RANDARRAY(B3,1,B2,B3)If we sort the sequential numbers (column F) by the random numbers (column G) we will end up with 45 unique random whole numbers.
Luckily with dynamic arrays we don’t need a separate range as in G2. We can use the SORTBY function and combine the formulas to sort the SEQUENCE range by the RANDARRRAY range.
The new formula in cell F2 is.
=SORTBY(SEQUENCE(B3,1,B2),RANDARRAY(B3,1,B2,B3))As per the image below this provides a list of 45 random, unique numbers.
But we don’t want 45 random numbers, we only wanted six as per cell B4.
We can add a new function to the formula to extract the first six random numbers.
The final formula for cell F2 is.
=TAKE(SORTBY(SEQUENCE(B3,1,B2),RANDARRAY(B3,1,B2,B3)),B4)
The TAKE function accepts a range (our list of 45 random whole numbers) and then allows you to specify a certain number of rows and columns to extract from the range. Positive numbers extract from the top of the range and negative numbers extract from the bottom of the range.
In our case we only need to specify the number of rows to extract from the top, as our range is a single column.
As you can see the formula became quite complex.
You can download the example file at the button below.
In the next post we will convert the four functions into a single custom function.
Please note: I reserve the right to delete comments that are offensive or off-topic.