Replacing the cell references with their values, we have: =INDEX(SORTBY(SEQUENCE(1000,1,10000,10),RANDARRAY(1000)),SEQUENCE(12)) In the worksheet shown the formula used to do this in F5 is: =INDEX(SORTBY(SEQUENCE(C5,1,C4,C6),RANDARRAY(C5)),SEQUENCE(C7)) This guarantees a specific number of unique values across a large range of possibilities. Worksheet exampleĪn alternative to the simple approach described above is to create a list of unique numbers with the SEQUENCE function, sort the list randomly, then extract a portion of the list. SEQUENCE returns an array of numbers between 1-15, RANDARRAY returns an array of 15 decimal values, and the SORTBY function sorts the output from SEQUENCE using the output from RANDARRAY. The result is a list of the 15 numbers between 1-15, sorted randomly. For example, to output 15 numbers sorted randomly, you can use a formula like this: =SORTBY(SEQUENCE(15),RANDARRAY(15)) SEQUENCE optionĪnother approach is to use the SEQUENCE function to generate a list of numbers, then use SORTBY and RANDARRAY to sort the list randomly. To work around this problem, and ensure a fixed number of unique random numbers, we can take a different approach with the SEQUENCE function as described below. The formula above works well if a specific number of results is not required. However, because the UNIQUE function will remove duplicates if they exist, the final count of numbers returned will change. To ensure that there are no duplicates, we can wrap RANDARRAY inside the UNIQUE function like this: =UNIQUE(RANDARRAY(12,1,10000,50000,TRUE)) For example, this formula returns 12 random numbers between 1000: =RANDARRAY(12,1,10000,50000,TRUE)Īlthough there is still a possibility of duplicates, the chance is much lower since there are 40,002 possible numbers. However, if you enter the formula and press F9 a few times, you will likely see some duplicate numbers because there is no guarantee that the numbers are unique.īy increasing the range of numbers generated, we can reduce the possibility of duplicates substantially. The rows argument sets how many numbers are returned, columns is 1, start is 1, end is 100, and integer is set to TRUE. For example, to generate 12 random numbers between 1 and 100, you can use RANDARRAY like this: =RANDARRAY(12,1,1,100,TRUE) The RANDARRAY function makes it easy to generate a list of random integers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |