How do I generate random lottery numbers in Excel?

How do I generate random lottery numbers in Excel?

Here's some code that will make better random numbers for you so you don't
keep getting the same answers (you'll obviously have to declare the variables
a, b, c, etc first), it generates 5 random numbers in the range 1-50:

Randomize
Seed2 = Timer
a = (Seed2 - Int(Seed2)) + Rnd() * 50
If a > 50 Then a = 50
Seed2 = Timer
b = (Seed2 - Int(Seed2)) + Rnd() * 50
If b > 50 Then b = 50
While b = a
Seed2 = Timer
b = (Seed2 - Int(Seed2)) + Rnd() * 50
If b = 50 Then b = 50
Wend
Seed2 = Timer
c = (Seed2 - Int(Seed2)) + Rnd() * 50
If c > 50 Then c = 50
While c = b Or c = a
Seed2 = Timer
c = (Seed2 - Int(Seed2)) + Rnd() * 50
If c > 50 Then c = 50
Wend
Seed2 = Timer
d = (Seed2 - Int(Seed2)) + Rnd() * 50
If d > 50 Then d = 50
While d = c Or d = b Or d = a
Seed2 = Timer
d = (Seed2 - Int(Seed2)) + Rnd() * 50
If d > 50 Then d = 50
Wend
Seed2 = Timer
e = (Seed2 - Int(Seed2)) + Rnd() * 50
If e > 50 Then e = 50
While e = d Or e = c Or e = b Or e = a
Seed2 = Timer
e = (Seed2 - Int(Seed2)) + Rnd() * 50
If e > 50 Then e = 50
Wend
Range("A1").Select
ActiveCell.FormulaR1C1 = a
Range("A2").Select
ActiveCell.FormulaR1C1 = b
Range("A3").Select
ActiveCell.FormulaR1C1 = c
Range("A4").Select
ActiveCell.FormulaR1C1 = d
Range("A5").Select
ActiveCell.FormulaR1C1 = e

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com

"Mark1809" wrote:

> How do I generate random lottery numbers in Excel?

Couple of caveats, here:

0) Everything that follows is useless for choosing or predicting numbers
in an external lottery. Playing 1-2-3-4-5 is just as sound a strategy as
playing a randomly generated number, assuming a fair game. If you're
generating your own lottery's random numbers, read on.

1) Unless a,b,c,d and e are declared as Integers or Longs (which may not
be "obvious" at first, this routine generates non-integers, which aren't
very useful for lottery numbers.

2) The claimed range is [1-50], the actual range is [0-50].

3) Using Timer to bias each number doesn't seem to me to "make better
random numbers" than using Randomize once. Since Seed2-Int(Seed2) >= 0,
it will tend to bias the results away from 0.

4) In MacXL, using Seed2-Int(Seed2) doesn't do anything since Timer
returns an integer.

5) In the case where Seed2-Int(Seed2) produces a value >0, using a cap
of 50 will tend to bias upward the number of times 50 is produced.

One way to get N random integers from a universe of M integers can be
found at

http://www.mcgimpsey.com/excel/randint.html



In article <[email protected]>,
Andrea Jones <[email protected]> wrote:

> Here's some code that will make better random numbers for you so you don't
> keep getting the same answers (you'll obviously have to declare the variables
> a, b, c, etc first), it generates 5 random numbers in the range 1-50:

I've run 122757 iterations using this macro and there is no bias towards high
or low values. I should have mentioned that all the variables are declared
as integers, I just wanted to give the gist of the code rather than the whole
thing. When I ran this so that the average number of occurrences for each
number was 2455 (122757 iterations) the number 50 occurred 2369 times.

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com

"JE McGimpsey" wrote:

> Couple of caveats, here:
>
> 0) Everything that follows is useless for choosing or predicting numbers
> in an external lottery. Playing 1-2-3-4-5 is just as sound a strategy as
> playing a randomly generated number, assuming a fair game. If you're
> generating your own lottery's random numbers, read on.
>
> 1) Unless a,b,c,d and e are declared as Integers or Longs (which may not
> be "obvious" at first, this routine generates non-integers, which aren't
> very useful for lottery numbers.
>
> 2) The claimed range is [1-50], the actual range is [0-50].
>
> 3) Using Timer to bias each number doesn't seem to me to "make better
> random numbers" than using Randomize once. Since Seed2-Int(Seed2) >= 0,
> it will tend to bias the results away from 0.
>
> 4) In MacXL, using Seed2-Int(Seed2) doesn't do anything since Timer
> returns an integer.
>
> 5) In the case where Seed2-Int(Seed2) produces a value >0, using a cap
> of 50 will tend to bias upward the number of times 50 is produced.
>
> One way to get N random integers from a universe of M integers can be
> found at
>
> http://www.mcgimpsey.com/excel/randint.html
>
>
>
> In article <[email protected]>,
> Andrea Jones <[email protected]> wrote:
>
> > Here's some code that will make better random numbers for you so you don't
> > keep getting the same answers (you'll obviously have to declare the variables
> > a, b, c, etc first), it generates 5 random numbers in the range 1-50:
>

Hello Andrea from Steved

I ran your Lottery function to get
8.736513138
32.51254272
47.04566193
17.73767471
47.16249466

As you can see I've got 47 twice

Question what am I doing wrong please and what is require please to generate
6 numbers.

Thankyou.


"Andrea Jones" wrote:

> I've run 122757 iterations using this macro and there is no bias towards high
> or low values. I should have mentioned that all the variables are declared
> as integers, I just wanted to give the gist of the code rather than the whole
> thing. When I ran this so that the average number of occurrences for each
> number was 2455 (122757 iterations) the number 50 occurred 2369 times.
>
> Andrea Jones
> http://www.allaboutoffice.co.uk
> http://www.stratatraining.co.uk
> http://www.allaboutclait.com
>
> "JE McGimpsey" wrote:
>
> > Couple of caveats, here:
> >
> > 0) Everything that follows is useless for choosing or predicting numbers
> > in an external lottery. Playing 1-2-3-4-5 is just as sound a strategy as
> > playing a randomly generated number, assuming a fair game. If you're
> > generating your own lottery's random numbers, read on.
> >
> > 1) Unless a,b,c,d and e are declared as Integers or Longs (which may not
> > be "obvious" at first, this routine generates non-integers, which aren't
> > very useful for lottery numbers.
> >
> > 2) The claimed range is [1-50], the actual range is [0-50].
> >
> > 3) Using Timer to bias each number doesn't seem to me to "make better
> > random numbers" than using Randomize once. Since Seed2-Int(Seed2) >= 0,
> > it will tend to bias the results away from 0.
> >
> > 4) In MacXL, using Seed2-Int(Seed2) doesn't do anything since Timer
> > returns an integer.
> >
> > 5) In the case where Seed2-Int(Seed2) produces a value >0, using a cap
> > of 50 will tend to bias upward the number of times 50 is produced.
> >
> > One way to get N random integers from a universe of M integers can be
> > found at
> >
> > http://www.mcgimpsey.com/excel/randint.html
> >
> >
> >
> > In article <[email protected]>,
> > Andrea Jones <[email protected]> wrote:
> >
> > > Here's some code that will make better random numbers for you so you don't
> > > keep getting the same answers (you'll obviously have to declare the variables
> > > a, b, c, etc first), it generates 5 random numbers in the range 1-50:
> >

Hi!

In the meantime......try this for 6 unique picks from 1 - 50

In A1 enter 1
In A2 enter 2

Select A1 and A2 and drag to fill down to A50

In B1 enter: =RAND()

Double click the fill handle to copy down to A50.

Select both columns A and B. Sort on column B. Take the 6 values from A1:A6.

Want to do it again? Hit function key F9 then sort.

Biff

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Andrea from Steved
>
> I ran your Lottery function to get
> 8.736513138
> 32.51254272
> 47.04566193
> 17.73767471
> 47.16249466
>
> As you can see I've got 47 twice
>
> Question what am I doing wrong please and what is require please to
> generate
> 6 numbers.
>
> Thankyou.
>
>
> "Andrea Jones" wrote:
>
>> I've run 122757 iterations using this macro and there is no bias towards
>> high
>> or low values. I should have mentioned that all the variables are
>> declared
>> as integers, I just wanted to give the gist of the code rather than the
>> whole
>> thing. When I ran this so that the average number of occurrences for
>> each
>> number was 2455 (122757 iterations) the number 50 occurred 2369 times.
>>
>> Andrea Jones
>> http://www.allaboutoffice.co.uk
>> http://www.stratatraining.co.uk
>> http://www.allaboutclait.com
>>
>> "JE McGimpsey" wrote:
>>
>> > Couple of caveats, here:
>> >
>> > 0) Everything that follows is useless for choosing or predicting
>> > numbers
>> > in an external lottery. Playing 1-2-3-4-5 is just as sound a strategy
>> > as
>> > playing a randomly generated number, assuming a fair game. If you're
>> > generating your own lottery's random numbers, read on.
>> >
>> > 1) Unless a,b,c,d and e are declared as Integers or Longs (which may
>> > not
>> > be "obvious" at first, this routine generates non-integers, which
>> > aren't
>> > very useful for lottery numbers.
>> >
>> > 2) The claimed range is [1-50], the actual range is [0-50].
>> >
>> > 3) Using Timer to bias each number doesn't seem to me to "make better
>> > random numbers" than using Randomize once. Since Seed2-Int(Seed2) >= 0,
>> > it will tend to bias the results away from 0.
>> >
>> > 4) In MacXL, using Seed2-Int(Seed2) doesn't do anything since Timer
>> > returns an integer.
>> >
>> > 5) In the case where Seed2-Int(Seed2) produces a value >0, using a cap
>> > of 50 will tend to bias upward the number of times 50 is produced.
>> >
>> > One way to get N random integers from a universe of M integers can be
>> > found at
>> >
>> > http://www.mcgimpsey.com/excel/randint.html
>> >
>> >
>> >
>> > In article <[email protected]>,
>> > Andrea Jones <[email protected]> wrote:
>> >
>> > > Here's some code that will make better random numbers for you so you
>> > > don't
>> > > keep getting the same answers (you'll obviously have to declare the
>> > > variables
>> > > a, b, c, etc first), it generates 5 random numbers in the range 1-50:
>> >

In article <[email protected]>,
Andrea Jones <[email protected]> wrote:

> I've run 122757 iterations using this macro and there is no bias towards high
> or low values.

I may be wrong, and I'm sure I'll be corrected if so, but...

Given

Dim a As Long
Dim i As Long
Dim arr(0 To 50) As Long
For i = 1 to 1000000
a = Rnd() * 50
arr(a) = arr(a) + 1
Next i

for the million trials, the arr(1) through arr(49) will each tally
approximately 2% or 20,000. arr(0) and arr(50) will each tally 1% or
10,000, since the coercion to a Long integer is equally likely to round
up or down (ignoring any bias of the rounding algorithm at
x.5000000000000000).

You then add Seed2 - Int(Seed2). If Timer, which Seed2 is based on, is
unbiased, then that factor will return [0-1) with an average of 0.5 (but
see below).

Assuming that's the case, the expected calculated, coerced, values of
a-e are now 0 - 51 before the corrections for a-e > 50, with fewer
zero's expected, and more 5x's. The correction for 51's will almost make
up for the reduced number due to splitting the rounding.

I also suspect that the assumption that Seed2 - Int(Seed2) is unbiased
is flawed for any particular run of the routine, assuming it takes
significantly less than a second - e.g., if the first Timer reading is
xx.70 and the whole process takes, say, 0.2 seconds, then the results of
repeatedly calculating the Seed2 - Int(Seed2) values will be between 0.7
and 0.9 which will bias a-e upward (i.e, no zeros will be produced), but
if the first timer reading occurs at xx.01 then the number of zeros will
be nearly as much as without the factor.

So I don't see what Seed2-Int(Seed2) does to give a "better" random
number than, say:

a = Int(Rnd() * 50) + 1

> I should have mentioned that all the variables are declared
> as integers

That would eliminate any effect of Seed2. Seed2 should probably be a
Single, Double, or Variant.

>I just wanted to give the gist of the code rather than the whole
> thing. When I ran this so that the average number of occurrences for each
> number was 2455 (122757 iterations) the number 50 occurred 2369 times.

Without the Seed2 correction, the expected number of 50's is 1227.57.
The Seed2 bias significantly raises that number.

Another way.

Just replace the "45" in this link with "50", and a hit of <F9> gives a new
"set".

http://tinyurl.com/8zr3a

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> In the meantime......try this for 6 unique picks from 1 - 50
>
> In A1 enter 1
> In A2 enter 2
>
> Select A1 and A2 and drag to fill down to A50
>
> In B1 enter: =RAND()
>
> Double click the fill handle to copy down to A50.
>
> Select both columns A and B. Sort on column B. Take the 6 values from
A1:A6.
>
> Want to do it again? Hit function key F9 then sort.
>
> Biff
>
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello Andrea from Steved
> >
> > I ran your Lottery function to get
> > 8.736513138
> > 32.51254272
> > 47.04566193
> > 17.73767471
> > 47.16249466
> >
> > As you can see I've got 47 twice
> >
> > Question what am I doing wrong please and what is require please to
> > generate
> > 6 numbers.
> >
> > Thankyou.
> >
> >
> > "Andrea Jones" wrote:
> >
> >> I've run 122757 iterations using this macro and there is no bias
towards
> >> high
> >> or low values. I should have mentioned that all the variables are
> >> declared
> >> as integers, I just wanted to give the gist of the code rather than the
> >> whole
> >> thing. When I ran this so that the average number of occurrences for
> >> each
> >> number was 2455 (122757 iterations) the number 50 occurred 2369 times.
> >>
> >> Andrea Jones
> >> http://www.allaboutoffice.co.uk
> >> http://www.stratatraining.co.uk
> >> http://www.allaboutclait.com
> >>
> >> "JE McGimpsey" wrote:
> >>
> >> > Couple of caveats, here:
> >> >
> >> > 0) Everything that follows is useless for choosing or predicting
> >> > numbers
> >> > in an external lottery. Playing 1-2-3-4-5 is just as sound a strategy
> >> > as
> >> > playing a randomly generated number, assuming a fair game. If you're
> >> > generating your own lottery's random numbers, read on.
> >> >
> >> > 1) Unless a,b,c,d and e are declared as Integers or Longs (which may
> >> > not
> >> > be "obvious" at first, this routine generates non-integers, which
> >> > aren't
> >> > very useful for lottery numbers.
> >> >
> >> > 2) The claimed range is [1-50], the actual range is [0-50].
> >> >
> >> > 3) Using Timer to bias each number doesn't seem to me to "make better
> >> > random numbers" than using Randomize once. Since Seed2-Int(Seed2) >=
0,
> >> > it will tend to bias the results away from 0.
> >> >
> >> > 4) In MacXL, using Seed2-Int(Seed2) doesn't do anything since Timer
> >> > returns an integer.
> >> >
> >> > 5) In the case where Seed2-Int(Seed2) produces a value >0, using a
cap
> >> > of 50 will tend to bias upward the number of times 50 is produced.
> >> >
> >> > One way to get N random integers from a universe of M integers can be
> >> > found at
> >> >
> >> > http://www.mcgimpsey.com/excel/randint.html
> >> >
> >> >
> >> >
> >> > In article <[email protected]>,
> >> > Andrea Jones <[email protected]> wrote:
> >> >
> >> > > Here's some code that will make better random numbers for you so
you
> >> > > don't
> >> > > keep getting the same answers (you'll obviously have to declare the
> >> > > variables
> >> > > a, b, c, etc first), it generates 5 random numbers in the range
1-50:
> >> >
>
>