PDA

View Full Version : [SOLVED] Differrences between Rnd and Randomize



sheeeng
06-21-2005, 08:26 PM
Hi all :wavey: (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&select_category=Greeting+%2F+Departing#),

What is the diferrences between Rnd and Randomize?

Why the Rnd will return same value after executed few time? :banghead:

Is there another way to force Rnd not to repeat same Rnd value in a period of time? :boohoo (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&select_category=Sad+%2F+Depressed#)

Thanks. :friends:

Jacob Hilderbrand
06-21-2005, 08:54 PM
Use Randomize to initialize the Random Number Generator in Excel based on the system timer. User Rnd to return a random number. Use Randomize before Rnd to make sure that the generated numbers are as random as possible (with Excel at least).

sheeeng
06-21-2005, 09:40 PM
Thanks. DRJ.
But is it possible to force Rnd not to repeat same Rnd value in a period of time?

Jacob Hilderbrand
06-21-2005, 09:47 PM
Just call Rnd with the same negative number.


MsgBox Rnd(-2)

But that's not really random is it. You could just make up the numbers you want.

Bob Phillips
06-22-2005, 01:30 AM
It is it possible to force Rnd not to repeat same Rnd value in a period of time?

Here is a way using worksheets.

First, ensure cell F1 is empty and goto Tools>Options and on the Calculation tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1

=IF(($F$1="")+AND(A1>0,COUNTIF($A$1:$A$20,A1)=1),A1,INT(1+20*(RAND())))
it should show a 0

Copy A1 down to A20.

Finally, put some value in F1, say an 'x', and all the random numbers will be generated.

They won't change.
To force a re-calculation, clear cell F1, edit cell A1, don't change it, just edit to reset to 0, copy A1 down to A20, and re-input F1.

Plus they don't repeatedly re-calculate.

sheeeng
06-22-2005, 02:13 AM
Next, type this formula into cell A1
IF(($F$1="")+AND(A1>0,COUNTIF($A$1:$A$20,A1)=1),A1,INT(1+20*(RAND())))
it should show a 0


xld, this part cannot work on my Excel 2002 (XP). How? :doh:

Bob Phillips
06-22-2005, 03:31 PM
xld, this part cannot work on my Excel 2002 (XP). How? :doh:

Hi Sheeng,

It is a formula, so it has to be preceded by =.

It works okay in 2002, that is my Excel version also.

sheeeng
06-22-2005, 06:52 PM
Here is a way using worksheets.

First, ensure cell F1 is empty and goto Tools>Options and on the Calculation tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1
=IF(($F$1="")+AND(A1>0,COUNTIF($A$1:$A$20,A1)=1),A1,INT(1+20*(RAND())))
it should show a 0

Copy A1 down to A20.

Finally, put some value in F1, say an 'x', and all the random numbers will be generated.

They won't change.
To force a re-calculation, clear cell F1, edit cell A1, don't change it, just edit to reset to 0, copy A1 down to A20, and re-input F1.

Plus they don't repeatedly re-calculate.

Yippie! :clap: It works. Thank you. :)
Another marked solved.

Thanks. :friends:

sheeeng
07-22-2005, 08:17 AM
How can I have a real Rnd function?

Bob Phillips
07-22-2005, 08:38 AM
How can I have a real Rnd function?

You have real problems here Sheeng. As is obvious, Excel is not a random number generator, but a pseudo-random number generator. In that, it suffers in-built limitations, but people much smarter than me say that Excel has a very poor algorithm to boot, so even within the inherent limitations, it is bad.

There are a lot of knowledgable people out ther if you want to Google for some of their musing on the subject, I would especially recommend Harlan Grove and David Braden. In addition, you might want to check this out
http://www.cse.csiro.au/poptools/. I am no statistical analyst, so I cannot comment on the tool, but it's PRNG is said to be good.

Justinlabenne
07-22-2005, 11:03 PM
I had found these codes awhile back and they seem to do fairly well considering Excel's limitations. I can't quote on how well, because I never have needed to produce pure random #'s save for test data.

The author is listed, as is the book the code is from. I came across it on a board awhile back. I don't own the book or know anything about the author, but it was touted in a few posts on the MrExcel Board.

In vb.net, I believe it is more feasible to achieve because you can use the Random Class, but I won't argue with a different opinion, as my knowledge of random # generation is limited, so I could be quite wrong.


Sub GenNUniqueRandom()
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
Dim list() As Long
Dim list1() As Long
Dim t As Long, i As Long, j As Long, k As Long
Dim lngTemp As Long
Dim num As Long
num = 10 'Number of Unique Random Numbers you need
t = 2000 'From a list of 1 to t numbers
ReDim list(1 To t)
For i = 1 To t
list(i) = i
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j)
list(j) = list(k)
list(k) = lngTemp
j = j - 1
Next
ReDim list1(1 To num)
For k = 1 To num
list1(k) = list(k)
Next
Range(cells(1, 1), cells(num, 1)).Value = _
Application.Transpose(list1)
End Sub


And one By Nate Oliver:


Option Explicit

Sub Ran()
Dim upr As Integer, lwr As Integer
Dim upr2 As Integer, lwr2 As Integer, cell As Range
Dim LastRow As Long, myrng As Range, c As Range
Dim SearchValue As String
upr = 9999 'upper1 integer limit
lwr = 1000 'lower1 integer limit
upr2 = 10 'upper1 integer limit
lwr2 = 99 'lower1 integer limit
Application.ScreenUpdating = False
For Each cell In [a1:a500]
Randomize
cell.Value = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
& Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
test: 'For uniqueness that is
LastRow = cell.Row - 1
If LastRow = 0 Then GoTo 1
Set myrng = Range("a1:a" & LastRow)
Set c = Range("A1:A" & LastRow).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
[a65536].End(xlUp) = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
& Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
GoTo test
End If
Set myrng = Nothing
Set c = Nothing
1:
Next cell
Application.ScreenUpdating = True
End Sub

sheeeng
09-13-2005, 01:35 AM
You have real problems here Sheeng. As is obvious, Excel is not a random number generator, but a pseudo-random number generator. In that, it suffers in-built limitations, but people much smarter than me say that Excel has a very poor algorithm to boot, so even within the inherent limitations, it is bad.

There are a lot of knowledgable people out ther if you want to Google for some of their musing on the subject, I would especially recommend Harlan Grove and David Braden. In addition, you might want to check this out
http://www.cse.csiro.au/poptools/. I am no statistical analyst, so I cannot comment on the tool, but it's PRNG is said to be good.

Thanks for the advise! I'm very poor in calculus. Btw, what is PRNG?

sheeeng
09-13-2005, 01:37 AM
I had found these codes awhile back and they seem to do fairly well considering Excel's limitations. I can't quote on how well, because I never have needed to produce pure random #'s save for test data.

The author is listed, as is the book the code is from. I came across it on a board awhile back. I don't own the book or know anything about the author, but it was touted in a few posts on the MrExcel Board.

In vb.net, I believe it is more feasible to achieve because you can use the Random Class, but I won't argue with a different opinion, as my knowledge of random # generation is limited, so I could be quite wrong.


Sub GenNUniqueRandom()
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
Dim list() As Long
Dim list1() As Long
Dim t As Long, i As Long, j As Long, k As Long
Dim lngTemp As Long
Dim num As Long
num = 10 'Number of Unique Random Numbers you need
t = 2000 'From a list of 1 to t numbers
ReDim list(1 To t)
For i = 1 To t
list(i) = i
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j)
list(j) = list(k)
list(k) = lngTemp
j = j - 1
Next
ReDim list1(1 To num)
For k = 1 To num
list1(k) = list(k)
Next
Range(cells(1, 1), cells(num, 1)).Value = _
Application.Transpose(list1)
End Sub

And one By Nate Oliver:


Option Explicit

Sub Ran()
Dim upr As Integer, lwr As Integer
Dim upr2 As Integer, lwr2 As Integer, cell As Range
Dim LastRow As Long, myrng As Range, c As Range
Dim SearchValue As String
upr = 9999 'upper1 integer limit
lwr = 1000 'lower1 integer limit
upr2 = 10 'upper1 integer limit
lwr2 = 99 'lower1 integer limit
Application.ScreenUpdating = False
For Each cell In [a1:a500]
Randomize
cell.Value = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
& Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
test: 'For uniqueness that is
LastRow = cell.Row - 1
If LastRow = 0 Then GoTo 1
Set myrng = Range("a1:a" & LastRow)
Set c = Range("A1:A" & LastRow).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
[a65536].End(xlUp) = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
& Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
GoTo test
End If
Set myrng = Nothing
Set c = Nothing
1:
Next cell
Application.ScreenUpdating = True
End Sub



Thanks, Justin! Great effort in there! I'll try it when free. :friends:

Bob Phillips
09-13-2005, 01:45 AM
Thanks for the advise! I'm very poor in calculus. Btw, what is PRNG?

Pseudo-Random-Number-Genereator, which is what most of these are.

sheeeng
09-13-2005, 01:49 AM
Pseudo-Random-Number-Genereator, which is what most of these are.

Thank you so much!