Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark1809
 
Posts: n/a
Default How do I generate random lottery numbers in Excel?

How do I generate random lottery numbers in Excel?
  #2   Report Post  
Andrea Jones
 
Posts: n/a
Default

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?

  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Couple of caveats, he

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 ,
Andrea Jones 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:

  #4   Report Post  
Andrea Jones
 
Posts: n/a
Default

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, he

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 ,
Andrea Jones 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:


  #5   Report Post  
Steved
 
Posts: n/a
Default

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, he

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 ,
Andrea Jones 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:




  #6   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
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, he

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 ,
Andrea Jones 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:



  #7   Report Post  
JE McGimpsey
 
Posts: n/a
Default

In article ,
Andrea Jones 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.
  #8   Report Post  
Ragdyer
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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, he

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 ,
Andrea Jones 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:




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I generate random, non-repeating numbers that don't change? bsquared0 Excel Discussion (Misc queries) 1 July 30th 05 04:22 AM
Random Numbers jannet Excel Discussion (Misc queries) 5 July 24th 05 03:52 PM
Text Columns Negative Numbers Upgrade Excel? Dust Bun Excel Discussion (Misc queries) 1 July 22nd 05 06:07 PM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Generate random numbers between two values and with a given mean pinosan Excel Worksheet Functions 2 March 7th 05 03:04 PM


All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"