same number appears in a random number generator

[Guest]

My formula for a lottery number selector is =INT(49*RAND()) but the problem
is that sometimes it generates the same numbers two or more times. I know
its something to do with the IF function, but can work out how to stop it.
Please Help!

 

[Biff]

Hi!

Here's one way:

Enter your numbers in A1:A49.......1;2;3;4;5...49

Enter this formula in B1:

=RAND()

Enter this formula in C1:

=INDEX(A$1:A$49,RANK(B1,B$1:B$49))

Select both B1 and C1 and copy down to row 49.

Use C1:C6 as your numbers.

To generate a new draw just press F9. Theoretically, it's possible to get
repeats but highly unlikely.

Biff

 

[Biff]

Here's another way that guarantees no repeats:

Enter your numbers in A1:A49.

Enter this formula in B1:

=RAND()

Copy down to B49.

Select both column A and column B.

Do a sort on column B. Either ascending or descending, it doesn't matter.

Use A1:A6 as your numbers.

Repeat the sort for a new draw.

Biff

 

[daddylonglegs]

My formula for a lottery number selector is =INT(49*RAND()) but the
problem
is that sometimes it generates the same numbers two or more times. I
know
its something to do with the IF function, but can work out how to stop
it.
Please Help!

I believe Biff's given you a couple of good solutions.....just to also
point out that using

=INT(49*RAND())

would not only generate repeats but would also sometimes give you zero
.....and never give you you 49.....!!

 

[joeu2004]

My formula for a lottery number selector is =INT(49*RAND())

FYI, if your intent is to generate numbers between 1 and 49 inclusive,
the formula should be 1+INT(49*RAND()).