![]() For other versions, you'll have to work out a different solution. How to do random sampling in Excel 2010 - 2019Īs only Excel for Microsoft 365 and Excel 2021 support dynamic arrays, the dynamic array functions used in the previous examples only work in Excel 365. A small change that makes a big difference is that you specify both the row_num and column_num arguments for the INDEX function: row_num is supplied by SEQUENCE and column_num by the array constant. The formula works with exactly the same logic as the previous one. ![]() INDEX(SORTBY( data, RANDARRAY(ROWS( data))), SEQUENCE( n), ) ![]() To select random rows with no repeats, build a formula in this way: Only works in Excel 365 and Excel 2021 that support dynamic arrays. Select random rows in Excel without duplicates Because the original data is already sorted in random order, we do not really care which positions to retrieve, only the quantity matters. For this, you supply the shuffled array to the INDEX function and request to retrieve the first N values with the help of the SEQUENCE function, which produces a sequence of numbers from 1 to N. As the result, your original data gets shuffled randomly.įrom the randomly sorted data, you extract a sample of a specific size. This array of random decimals is used as the "sort by" array by the SORTBY function. The ROWS function counts how many rows your data set contains and passes the count to the RANDARRAY function, so it can generate the same number of random decimals: Here's a high-level explanation of the formula's logic: the RANDARRAY function creates an array of random numbers, SORTBY sorts the original values by those numbers, and INDEX retrieves as many values as specified by SEQUENCE. =INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))įor the sake of convenience, you can input the sample size in a predefined cell, say C2, and supply the cell reference to the SEQUENCE function: INDEX(SORTBY( data, RANDARRAY(ROWS( data))), SEQUENCE( n))įor example, to get 5 unique random names from the list in A2:A10, here's the formula to use:
0 Comments
Leave a Reply. |