user1410222
user1410222

Reputation: 31

Generate random numbers which do not repeat by using formulas

I want to generate 4 random numbers between 0 and 20 which do not repeat among themselves. i.e. For each subsequent generated random number, it must exclude the value of any previously generated random numbers.

E.g.

The 2nd generated random number must not be equal to the 1st one, the 3rd one must not be equal to the 1st and 2nd ones, and finally the 4th one must not be equal to the 1st, 2nd, and 3rd ones.

How to achieve that using Excel formulas?

Upvotes: 2

Views: 1742

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Put this in A2:

=AGGREGATE(15,6,(ROW($1:$21)-1)/(COUNTIF($A$1:A1,(ROW($1:$21)-1))=0),RANDBETWEEN(1,22-ROW(1:1)))

And copy down four cells.

enter image description here

Upvotes: 7

Related Questions