Encountering repeated values in a list of random values depends on two factors:
- The range by which random numbers are selected (e., the min and max values)
- The number generated values
If you restrict the min/max range to fall between 1 and 10, then generate 100 values, the likelihood of encountering repeats is almost guaranteed.
Generating 10 random values between 1 and 1 million, the odds of encountering repeated values is as likely as winning the lottery three times in a row after discovering the cure for cancer in the letters of your alphabet soup.
If you must use a narrow range of values and require a result with no repeats, place the RANDARRAY function inside a UNIQUE function.
Using this technique, you may encounter (actually, you will more than likely encounter) two issues:
- The result set may shrink and grow with repeated F9 This is because the RANDARRAY function may generate repeated values. If this happens, the UNIQUE function tosses the repeats out. New random, non-repeated values will not take their place.
- A recalculated result array may generate a #SPILL!
SEQUENCE & SORTBY to the Rescue
To work around these issues, you can use the SEQUENCE function with the SORTBY function.
Using the SEQUENCE function, I can generate a list of numbers from 1 to 10 with the following formula.
This generates a list of 10 numbers in a single column (10 rows) starting at 1 and incrementing by 1.
To generate a list of 10 even numbers in a single column starting with 2, enter the following:
=SEQUENCE(10, , 2, 2)
Neither of these examples will ever generate repeats
What if we tool the results of the SEQUENCE function and sorted them in a random order using the SORTBY function?
The SORTBY function can be used to sort an array (ex: our SEQUENCE results) by another array. The second array can be an array of random numbers generated by the RANDARRAY function.
The array of random numbers must be of the same dimension as the SEQUENCE function result.
If we used SEQUENCE to generate even numbers between 2 and 20…
=SEQUENCE(10, , 2, 2)
…we nest the above formula in a SORTBY function and define the sort array using the RANDARRAY function.
=SORTBY(SEQUENCE(10, , 2, 2), RANDARRAY(10) )
The size is fixed by SEQUENCE and the issue of encountering a #SPILL! error is eliminated.