non repeating Random number generation
Ken Dordi
numbers chosen from 1 to 49 so that all 49 numbers are
randomly jumbled but there are no two numbers the same.
yes you guessed its for a lottery worksheet im trying to
compile, if someone out there has an excel worksheet which
does this id be very grateful.
Harald Staff
The common way to do this is to enter the numbers in A column, here A1:A49.
In B1:B49 formula =RAND(). Sort by B column, top x in A column are winners.
Press F9 and repeat for a new result.
HTH. Best wishes Harald
"Ken Dordi" <ken....@bskyb.com> skrev i melding
news:07da01c2e968$d482d5d0$a101...@phx.gbl...
Ken Wright
time I'll happily mail it on, though it is currently set for 6 and is easily
adaptable. If you wanted to do the formatting etc yourself then the macro
that will do it as follows (Courtesy of Tom Ogilvy):-
I'll send you the workbook as is, but if you want me to edit the ranges or
amount of numbers then just holler. It is also set up to draw names from a
hat, and in both cases (Lottery or Draw) depending on which button you
press, will run quickly or slowly for effect.
Sub RandLotteryNumbers2()
'If you want unique random numbers,
'i.e. you want to shuffle the numbers 1 to 49
'Change 50000 For k = 1 To 50000 to change time delay
'to either a smaller or larger number to speed up or slow down.
'I tested on a 900 Mhz machine and you could see the numbers change.
'
Dim i, choice, balls(1 To 49)
Dim lngArr(1 To 49) As Long
Randomize 'Timer
For i = 1 To 49
balls(i) = i
Next
For i = 1 To 49
choice = 1 + Int((Rnd * (49 - i)))
temp = balls(choice)
balls(choice) = balls(50 - i)
balls(50 - i) = temp
Next
i = 0
' identify your cells here - just 6 cells
Set Rng = Range("A1:F1")
For Each cell In Rng
i = i + 1
For j = 1 To 20
cell.Value = Int(Rnd() * 49 + 1)
For k = 1 To 50000
dum = (dum * k) ^ 5
Next k
DoEvents
Next j
cell.Value = balls(i)
Next
Range("A1:F1").Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Orientation:=xlLeftToRight
Range("H1").Select
End Sub
--
Regards
Ken....................... Win XP / XL2K & XLXP
----------------------------------------------------------------------------
Public Service Request - It is very much appreciated
in text-only groups if you don't attach files - Thanks
----------------------------------------------------------------------------
"Ken Dordi" <ken....@bskyb.com> wrote in message
news:07da01c2e968$d482d5d0$a101...@phx.gbl...
ken dordi
repeated numbers, ive also tried to work out a way to take
the numbers randomly from an array of 49 numbers but the
repeating with the rand function again hinders! even the
randbetween function which is hidden inside the data
analysis menu option produces repeating numbers arghhh!
on checking through the internet there are several forums
that suggest very complex mathmatical workrounds which are
way over my head! so im hoping someone brainy out there
has managed to come up with an excel spreadsheet solving
it.
>
J.E. McGimpsey
duplicates if you have your cells formatted to a fixed number of
decimal places (the display rounds the value, but the underlying value
used in calculations is not).
While I'm sure that you could find a template or two in the archives,
http://google.com/advanced_group_search?q=group:*excel*
I suspect that most of the "someones brainy" are not working on lottery
spreadsheets.
In article <06e801c2e96d$02a4d860$a301...@phx.gbl>, ken dordi
ken.dordi
the workbook as im not too certain how to implement the
macro utility within my own worksheet.in answer to the
question id like to choose 49 numbers each time although
the 49 random names out the hat is exactly what i want,
how does it recalculate each week by pressing f9?
many thanks for your help you are a life saver!
>
Harlan Grove
>RAND() doesn't produce duplicate numbers, although they may display as
>duplicates if you have your cells formatted to a fixed number of
>decimal places (the display rounds the value, but the underlying value
>used in calculations is not).
Picky: all pseudorandom number generators repeat numbers, but the cycle for
Excel's RAND is much greater than the number of rows, so unlikely in the extreme
that the OP really is seeing duplicates.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
J.E. McGimpsey
<hrl...@aol.com> wrote:
> Picky: all pseudorandom number generators repeat numbers, but the cycle for
> Excel's RAND is much greater than the number of rows, so unlikely in the
> extreme
> that the OP really is seeing duplicates.
Very true - thanks for the correction.
Myrna Larson
Do you know what the length of the cycle is for RAND() ? It's 2^24 for the VBA Rnd() function,
but I think the worksheet function may be different.
Myrna Larson
Jay Petrulis
Hi,
If you would like a worksheet solution, search google for the
hypergeometric sampling function with the author Dave Braden. He has
two routines that are very nice.
Also, the code below will do what you want. It will not repeat
numbers in any sample, but on my tests it is slower than Dave's code,
plain and simple.
'------
Function SampleNoReplace(SampleSize As Long, LowerBound As Long,
UpperBound As Long, _
Optional IsStatic As Boolean = False, Optional OneRow As Boolean =
False)
Dim PopulationCollection As New Collection
Dim SampleArray
Dim temp As Long
Dim i As Long
Dim j As Long
If Not (IsStatic) Then Application.Volatile True
Randomize
If UpperBound < LowerBound Then
temp = UpperBound
UpperBound = LowerBound
LowerBound = temp
End If
If (SampleSize > (UpperBound - LowerBound + 1) Or SampleSize <= 0)
Then
SampleNoReplace = CVErr(xlErrValue)
Exit Function
End If
ReDim SampleArray(1 To SampleSize) As Long
For i = LowerBound To UpperBound
PopulationCollection.Add i
Next i
For i = 1 To SampleSize
With PopulationCollection
j = Int(Rnd * .Count) + 1
SampleArray(i) = .Item(j)
.Remove j
End With
Next i
Set PopulationCollection = Nothing
If OneRow Then
SampleNoReplace = SampleArray
Else
SampleNoReplace = Application.Transpose(SampleArray)
End If
End Function
'------
Select cells A1:F1 and in the formula bar type
=SampleNoReplace(9,1,49,,1)
Hold the Ctrl-Shift keys down and then press Enter. If done properly,
Excel will surround the formula with braces, {}.
Hitting <F9> will cause another "draw" of the lottery.
=SampleNoReplace(9,1,49,1,1) will cause the numbers to remain static,
so you can store them on the worksheet, and resample in another set of
cells for "next week's" drawing. Good luck. :)
Bye,
Jay
Harlan Grove
>Do you know what the length of the cycle is for RAND() ? It's 2^24 for the
VBA Rnd()
>function, but I think the worksheet function may be different.
It seems to be greater, but I don't know what it is. It has to be less than
2^53, which is the number of binary digits of floating point precision.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Stephen Dunn
http://www.ky.hkkk.fi/~k24690/formal/excel/excelrandom.html
RAND can produce up to 1 million different values.
"Harlan Grove" <Hrl...@aol.com> wrote in message
news:3_yda.11698$ja4.8...@bgtnsc05-news.ops.worldnet.att.net...
Stephen Dunn
"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message
news:130320030736322991%jemcg...@mvps.org...
> RAND() doesn't produce duplicate numbers, although they may display as
> duplicates if you have your cells formatted to a fixed number of
> decimal places (the display rounds the value, but the underlying value
> used in calculations is not).
Unless Tools>Options>Calculation>Pecision as displayed is turned on.
David J. Braden
The ATP prng repeats numbers like *crazy*, but with no cyclicity that I
can uncover. E.g., generate 25000 uniform variates from the ATP. Fewer
than half will occur only one time. An amazing number occure at least
three times. It is, without doubt, the most perverse attempt at a
generator I have ever seen in a commercial package.
Dave B
In article <833ca.676$S4....@www.newsranger.com>,
Harlan Grove<hrl...@aol.com> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Harlan Grove
>
>There's a collection of Knowledge Base articles on RAND and Rnd here:
>
>http://www.ky.hkkk.fi/~k24690/formal/excel/excelrandom.html
>
>RAND can produce up to 1 million different values.
Text straight from this site (and so straight from the referenced KB article):
"random_number=fractional part of (9821 * r + 0.211327),
where r = the previous random number
This formula will provide up to 1 million different numbers."
One problem is that you believe what Microsoft publishes in their documentation
and Knowledge Base. Long experience working with Microsoft docs and software
will eventually teach you that Microsoft docs are not to be relied upon for
details of implementation.
To test the assertion above, I used the following.
Sub foo()
Dim n As Long, fd As Variant
fd = FreeFile
Open "D:\tmp\foo.out" For Output As #fd
For n = 1 To 2000000
If n Mod 25000 = 0 Then Application.StatusBar = n
Print #fd, Format(Evaluate("=RAND()"), "0.00000000000000E-000")
Next n
Close #fd
Application.StatusBar = False
End Sub
Then I ran the following console session (using several GNU text processing
tools).
tmp (2)% sort foo.out > foo.sorted
tmp (3)% uniq foo.sorted > foo.uniq
tmp (4)% wc -l foo.*
2000000 foo.out
2000000 foo.sorted
2000000 foo.uniq
6000000 total
tmp (5)%
which means that the macro above generated no duplicate entries. Next I did this
for 20,000,000 RAND values.
tmp (7)% sort foo.out > foo.sorted
tmp (8)% uniq foo.sorted > foo.uniq
tmp (9)% wc -l foo.*
20000000 foo.out
20000000 foo.sorted
20000000 foo.uniq
60000000 total
tmp (10)%
So still no duplicates at 20 million iterations.
If Microsoft's algorithm can be taken at face value, then it's likely the period
is on the order of 2^40 because they're taking advantage of floating point
rounding error to add to the apparent randomness. However, it's still a linear
congruential generator. Since the 9821 multiplicative factor could eat up to 14
bits for the discarded integer part of the 9821 * r + 0.211327 value, that
leaves the 39 highest fractional bits.
So the situation is much better than a mere 1 million different values, but
would still suffer from the failings of all linear congruential generators.
--
Public Service Announcement
Stephen Dunn
"Harlan Grove" <hrl...@aol.com> wrote in message
news:WDMda.1572$S4....@www.newsranger.com...
> "Stephen Dunn" wrote...
> >
> >There's a collection of Knowledge Base articles on RAND and Rnd here:
> >
> >http://www.ky.hkkk.fi/~k24690/formal/excel/excelrandom.html
> >
> >RAND can produce up to 1 million different values.
> ..
>
> Text straight from this site (and so straight from the referenced KB
article):
>
> "random_number=fractional part of (9821 * r + 0.211327),
> where r = the previous random number
>
> This formula will provide up to 1 million different numbers."
>
> One problem is that you believe what Microsoft publishes in their
documentation
> and Knowledge Base. Long experience working with Microsoft docs and
software
> will eventually teach you that Microsoft docs are not to be relied upon
for
> details of implementation.
>
I have the experience to know that MS docs are unreliable, but I don't have
the time to test everything that they state! A million different numbers
from a linear congruential generator is more than sufficient for my needs,
and for anything I've seen in the group, so I felt no compulsion to question
it before passing it on.
<snip>
