How to generate sets of more than one random numbers from a given set of numbers. For example, lets say a given set of numbers is {1,2,3,4,5,6}. I want to create a 100 sets of two numbers (or 3 or 4) randomly selected from the given set of numbers. In excel, we can generate one random number and use the autofill to generate any number of one random number.

I tried EXCEL function "=RAND()". But it generates only one number at a time.

How to generate sets of more than one random numbers from a given set of numbers. For example, lets say a given set of numbers is {1,2,3,4,5,6}. I want to create a 100 sets of two numbers (or 3 or 4) randomly selected from the given set of numbers. In excel, we can generate one random number and use the autofill to generate any number of one random number.

I tried EXCEL function "=RAND()". But it generates only one number at a time.

• excel
• excel-formula

• With or without replacement? – BigBen Commented Mar 5 at 17:48
• with replacement – Mr. MBB Commented Mar 5 at 17:49

2 Answers 2

Using INDEX and RANDARRAY. The toy example uses =SEQUENCE(6) as the given number set in cell A1:

=INDEX(A1#,RANDARRAY(2,100,1,ROWS(A1#),TRUE))

The idea is to generate a random array of indices into the given set. This creates a 2x100 array where each column is a desired set:

If your Excel version does not support RANDARRAY, then use RANDBETWEEN, inserting the formula in as many rows/columns as desired:

=INDEX($A$1:$A$6,RANDBETWEEN(1,ROWS($A$1:$A$6)))

Generating Sets of Random Numbers

=LET(data,A2:A21,sets,B2,size,C2,
    d,TOROW(data,3),
    c,COLUMNS(d),
    DROP(REDUCE("",SEQUENCE(sets),LAMBDA(rr,r,
        VSTACK(rr,TAKE(SORTBY(d,RANDARRAY(,c)),,size)))),1))