One method is to enter the core set of numbers to choose from in one column, and use the RAND function to assign random decimal values to each number in another column. If you have a version of Excel without SEQUENCE, SORTBY, and RANDARRAY, you can take a more manual approach. Legacy Excelĭynamic array formulas are a new feature in Excel. For example, once you have a list of random numbers without duplicates, you can use those numbers with the INDEX function to create a random list of names without duplicates. The approach described above can be applied to other related problems. ![]() The numbers are guaranteed to be unique because the original array created by SEQUENCE contains no duplicates. With these inputs, INDEX returns the first 12 rows from the randomly sorted array as a final result. The first 10 numbers in the array look like this: ) The result from SEQUENCE is an array that contains 1000 numbers. The step argument is given as 10 to return numbers that are multiples of 10. The SEQUENCE function is configured to generate 1000 numbers starting at 10000. Working from the inside out, the core of the formula is this: SORTBY(SEQUENCE(1000,1,10000,10),RANDARRAY(1000))Īt a high level, the SORTBY function is used to sort the output from SEQUENCE randomly. 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 |