Selecting Ten Random Numbers

Fawn needs a way to select 10 whole numbers from a pool of 150 potential numbers. For instance, if the pool contains the sequential whole numbers of 201 through 350, she needs to select 10 numbers from that pool, at random.

The traditional way to do this is to use a very simple function in a cell, like this:

=RANDBETWEEN(201,350)

This returns a single random value between 201 and 350, inclusive. Copy the formula down for 9 more cells, and you end up with the 10 desired random values. The problem, though, is that you could end up with duplicate values. This is especially true if the pool of potential numbers is reduced in size and the number of desired values increases.

Continuing down the traditional path, this potential of duplication is reduced significantly by using the RAND function instead of RANDBETWEEN. This is because RAND returns a value greater than or equal to 0 and less than 1, at a precious of 15 decimal places. Thus, you could use the following formula in a cell to return a number in the range of 201 to 350:

=INT(RAND()*150)+201

Copy this down to 9 more cells, and you end up with your 10 desired values, with virtually zero chance of duplication.

With the advent of the newest dynamic array functions, the approach can be simplified quite a bit. Consider the following formula:

=RANDARRAY(10,1,201,350,1)

This returns 10 integer values between 201 and 250. The problem here is the same as it is with RANDBETWEEN—there could easily be duplicates. Some suggest that you can remove the problem by enclosing the function within the UNIQUE function:

=UNIQUE(RANDARRAY(10,1,201,350,1))

This does ensure that no duplicates are returned, but you are no longer guaranteed to always have 10 values, which goes against what Fawn needs.

The best solution is to not force RANDARRAY to use integer values. If you allow it to work with decimal values, it acts very much like the RAND function, returning an array of values between 0 and 1. To see how powerful this can be, consider the following formula:

=TAKE(SORTBY(SEQUENCE(150,1,201),RANDARRAY(150)),10)

The SEQUENCE function returns an array of values 201 through 350 and the RANDARRAY function returns an array of 150 random values between 0 and 1. The SORTBY function is then used to sort the integer sequence by the results of the random array. Finally, the TAKE function returns the first 10 values from the sorted array. The result is always 10 values, always in random order, with absolutely no chance of duplicates.

The SEQUENCE, SORTBY, RANDARRAY, and UNIQUE functions are available in Excel 2021 and 2024. To use the TAKE function you will need Excel 2024. All of these functions are available to users of Microsoft 365.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2319) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.