same number appears in a random number generator

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!

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

"Carmel" <[email protected]> wrote in message
news:[email protected]...
> 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!

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Carmel" <[email protected]> wrote in message
> news:[email protected]...
>> 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!
>
>

Originally Posted by Carmel

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.....!!

Carmel wrote:
> 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()).