How to get repeatable Excel RAND sequence?
Joe User
subsequent calls to RAND result in a repeatable sequence?
I have tried seeding the VBA Rnd function, to no avail. No surprise that
the two are unrelated. It was a "hail Mary" try.
Although I know how to get a repeatable Rnd sequence, I do not want to use
the VBA Rnd function for my purposes.
I could also easily implement my own PRNG, even using the Wichman-Hill(1982)
algorithm described in support.microsoft.com/kb/828795. (Actually, I
already have.) But that, too, does not suit my purposes.
I want to use Excel RAND per se; no alternatives.
KB 44738 suggests that there might be a RANDOMIZE variable somewhere;
perhaps in an INI file, perhaps in the Registry now, or perhaps a command
line switch. But I have not (yet) succeeded in locating such a variable for
Excel 2003.
I did try executing Excel in safemode, to no avail. Another
"hail Mary" try. I even tried the command line switch /randomize=0, to no
avail. A "shot in the dark".
(KB 44738 refers to earlier versions of Excel in which RAND did produce a
repeatable sequence by default, and it required setting the RANDOMIZE
variable to change that.)
Alternatively, does anyone know exactly how the Excel 2003 RAND function is
seeded?
Presumably by some manipulation of "system time"; but that is subject to
interpretation. I can speculate as well as anyone. I am looking for the
actual algorithm. KB 828795 does not explain that; and my Google searches
have been fruitless (so far).
Charles Williams
I don't know of a way to seed the 2003 RAND function, or what the
algorithm is, but
http://support.microsoft.com/kb/828795
may help.
When I want a repeatable sequence of random numbers I generate a
range using RAND() and then copy-paste values to freeze the results.
Charles Williams
Joe User
> http://support.microsoft.com/kb/828795 may help.
Y'don't 'spose that's the very same support.microsoft.com/kb/828795 that I
referred to in my posting? ;-) Y'had to read all the way down to the 4th
paragraph (6th sentence).
No, it does not help.
> When I want a repeatable sequence of random numbers
> I generate a range using RAND() and then copy-paste
> values to freeze the results.
Yeah, I 'spose I could make that work for my purposes.
I am still interested in how to seed RAND or how it is seeded, if only to
satisfy my curiosity.
----- original message -----
"Charles Williams" <Cha...@DecisionModels.com> wrote in message
news:292cq595ehdu5vk1p...@4ax.com...
----- previous message -----
"Joe User" <joeu2004> wrote in message
news:e4ilG2Ny...@TK2MSFTNGP06.phx.gbl...
> Is there any way to seed (initialize) the Excel 2003 RAND function so that
> subsequent calls to RAND result in a repeatable sequence?
>
Joe User
>> When I want a repeatable sequence of random numbers
>> I generate a range using RAND() and then copy-paste
>> values to freeze the results.
>
> Yeah, I 'spose I could make that work for my purposes.
Actually not. I was wondering why I didn't think of that rather obvious
approach. When I remembered why, I also realized that even disabling the
initial seeding (presumably based on "system time") would not satisfy my
needs.
I was able to learn some details about VB Rnd because it does produce a
repeatable sequence unless you call Randomize. But what I learned was made
possible only because the default seed is documented in KB 231847.
Since KB 828795 fails to document the default seed (I suspect now there is
none), it does not help to disable the initial seeding. I am still stuck
with trying to infer the 3 factors in the Wichman-Hill algorithm,
ass-u-me-ing that KB 828795 is correct. I had developed an inferential
algorithm in Dec'09. And for that, any RAND result will do; I do not even
need a sequence.
I was never entirely satisfied with the results of the inferential approach
because it was always off in some low-order binary bits. The difference was
small enough to be "close enough for government work", but too large to be
explained by some nuance of computer arithmetic.
I was hoping that I now had a new approach to try. But I am wrong.
----- original message -----
"Joe User" <joeu2004> wrote in message
news:%23%23DYYSQy...@TK2MSFTNGP06.phx.gbl...
Hans Terkelsen
"Joe User" <joeu2004> wrote in message news:%23yVpf8S...@TK2MSFTNGP05.phx.gbl...
> "Joe User" <joeu2004> wrote:
...
> I was able to learn some details about VB Rnd because it does produce a
> repeatable sequence unless you call Randomize. But what I learned was made
> possible only because the default seed is documented in KB 231847.
>
> Since KB 828795 fails to document the default seed (I suspect now there is
> none), it does not help to disable the initial seeding. I am still stuck
> with trying to infer the 3 factors in the Wichman-Hill algorithm,
> ass-u-me-ing that KB 828795 is correct. I had developed an inferential
> algorithm in Dec'09. And for that, any RAND result will do; I do not even
> need a sequence.
>
> I was never entirely satisfied with the results of the inferential approach
> because it was always off in some low-order binary bits. The difference was
> small enough to be "close enough for government work", but too large to be
> explained by some nuance of computer arithmetic.
>
> I was hoping that I now had a new approach to try. But I am wrong.
...
Hi Joe.
I have tried some of the same things, that you have.
Just out of curiosity.
The VBA Rnd article gives the full story, it is easy to reconstruct Rnd in the worksheet.
However the RAND() article gives an overview only, I think.
If that was the full story then
=30269*30307*30323*RAND() should be close to an integer, which it is not.
I could not locate more info, so I gave up!
Good luck Hans T.
Joe User
> The VBA Rnd article gives the full story,
> it is easy to reconstruct Rnd in the worksheet.
I would be surprised if we can duplicate the VBA Rnd sequence using only
Excel formulas, if that is what you mean. At least, I don't think it is
"easy".
When I tried, the Excel result does exactly duplicate the first VBA Rnd
result (using the default seed), and they agree within 6 decimal places for
the second Rnd result. (Note: close, but not exactly the same.) However,
they are significantly different for the third random number, and they begin
to diverge significantly after that.
The reason is: the VBA algorithm potentially requires as much as 55 bits of
precision, and apparently the VBA Rnd implementation does provide that [2].
But Excel computation offers at most 53 bits of precision. We get lucky
with the computation for the first random number: x*a+c requires only 49
bits, where x=327680, a=1140671485 and c=12820163. But for the second
random number, x*a+c requires 54 bits because x=11837123.
The following are the first 10 numbers generated by an Excel algorithm and
the VBA Rnd function:
Excel rnd VBA Rnd
0.7055475115776060 0.7055475115776060
0.5334241390228270 0.5334240198135370
0.5581560134887690 0.5795186161994930
0.5320560932159420 0.2895624637603760
0.7160687446594230 0.3019480109214780
0.0968921184539795 0.7747400999069210
0.4058379530906680 0.0140176415443420
0.3854335546493530 0.7607235908508300
0.9148474335670470 0.8144900202751150
0.3595037460327150 0.7090378999710080
This is based on the following "easy" Excel implementation:
A1, a: 1140671485
A2, c: 12820163
A3, m, 2^24: 16777216
B6, x: 327680
A7, x*a+c: =B6*$A$1+$A$2
B7, x = x mod m: =A7-$A$3*INT(A7/$A$3) [1]
C7, Excel rnd: =B7/$A$3
D7, VBA Rnd
Copy A7:C7 down through A16:C16.
I fill in D7:D16 from the Workbook_Open() function to ensure that the
first 10 VBA Rnd values are used and never changed by recalculation.
With Worksheets("sheet1")
'16 dp to accommodate Rnd results of the form 0.0x...x
.Range("d7:d16").NumberFormat = "0." & String(16, "0")
With .Range("d7")
For i = 0 To 9
.Offset(i, 0) = Rnd
Next
End With
.Range("d7:d16").Columns.AutoFit
End With
-----
Endnotes
[1] We cannot use MOD(x,m) because that trips over the defect that is
described (poorly) in KB 119083. I think there is a better description in
some KB. But I cannot remember which one.
[2] Based on KB 231847, the following VBA implementation duplicates the
entire sequence of 16,777,215 values returned by VBA Rnd based on the
default seed.
Type cType
c As Currency
End Type
Type l64Type 'little-endian (Pentium-compatible)
lsb As Long
msb As Long
End Type
Function myRnd() As Single
'do not use 16777216! -- cannot copy line
Const fMax As Single = 16777216@
Const a As Currency = 114067.1485@
Const c As Currency = 1282.0163@
Static x1 As cType, notFirst As Integer
Dim lx1 As l64Type
If notFirst = 0 Then x1.c = 32.7680@: notFirst = 1
x1.c = 10000@ * x1.c * a + c
'effectively: x1 = x1 mod 1677.7216@
'alternative: x1 = x1 - 1677.7216@ * Int(x1 / 1677.7216@)
LSet lx1 = x1
lx1.msb = 0: lx1.lsb = lx1.lsb And &HFFFFFF
LSet x1 = lx1
myRnd = CSng(x1.c * 10000@) / fMax
End Function
If you do not understand the "Lset lx1 ... Lset x1" implementation, replace
those 3 lines with the alternative.
Representing x1, a and c by 1/10000 not only allows us to use the full
63-bit precision of type Currency, but also it avoids arithmetic overflows
when x1 is sufficiently large. That situation arises even in the second
call to Rnd, when x1 is initially 11837123 and x*a+c requires 54 bits of
precision.
----- original message -----
"Hans Terkelsen" <dk> wrote in message
news:ut65Xhny...@TK2MSFTNGP04.phx.gbl...
Joe User
> However the RAND() article gives an overview only, I think.
I do not think so. I have found many similar explanations of the
Wichman-Hill(1982) algorithm. Most are similar to KB 828795 in effect, if
not exactly the same. (Caveat: Some are obviously wrong in some details,
notably the wiki explanation.)
> If that was the full story then
> =30269*30307*30323*RAND() should be close to an
> integer, which it is not.
Thanks for that insight. You might have stumbled onto the key reason why it
has been difficult to infer the 3 internal factors given a RAND() result.
(Well, it is still difficult. But I mean: why I always had infinitesimal
differences when I tried to apply the inferred factors.)
Consider the following two implementations of the Wichman-Hill(1982) method
of computing the random value, given the 3 internal factors.
Function sngRAND(ix As Single, iy As Single, iz As Single) As Single
ix = ix / 30269! + iy / 30307! + iz / 30323!
sngRAND = ix - Int(ix)
End Function
Function dblRAND(ix As Double, iy As Double, iz As Double) As Double
ix = ix / 30269# + iy / 30307# + iz / 30323#
dblRAND = ix - Int(ix)
End Function
You are correct that 30269*30307*30323*dblRand() is "always" close to an
integer, for random values of IX=1,...,30268, IY=1,...,30306 and IZ=1,30322.
(At least for the many random values that I tried.)
But for the same values of IX, IY and IZ, 30269*30307*30323*sngRand() is
often not close to an integer.
For example, for IX=23695, IY=9539 and IZ=3705, 30269*30307*30323*dblRand()
is about 6112669340694.00, but 30269*30307*30323*sngRand() is about
6112668665232.35.
Since the latter is typical of the non-integral results that we see for
30269*30307*30323*RAND(), I suspect that RAND() uses type Single for
intermediate computations internally(!).
And there might also be other internal computational factors that could
contribute to the unexpected results.
----- original message -----
"Hans Terkelsen" <dk> wrote in message
news:ut65Xhny...@TK2MSFTNGP04.phx.gbl...
>
Joe User
> Since the latter is typical of the non-integral results
> that we see for 30269*30307*30323*RAND(), I suspect that
> RAND() uses type Single for intermediate computations
> internally(!).
That may or may not be true (probably not), but RAND() certainly is not
implemented like my sngRAND().
If it were, the binary pattern of the RAND() result would look something
like &h3FEB1B11,C0000000. That is, the mantissa would end in at least 29
zero bits.
Instead, the RAND() result typically looks like &h3FE13AF7,E7F36098,
utilizing the full 53 bits of the type Double precision.
----- original message ------
"Joe User" <joeu2004> wrote in message
news:enholQMz...@TK2MSFTNGP04.phx.gbl...
Dana DeLouis
observation.
> Excel rnd vs. VBA Rnd
> <just the last two entries below - loops 9&10>
> 0.9148474335670470 0.8144900202751150
> 0.3595037460327150 0.7090378999710080
In a vba procedure to do your Worksheet, if I declare my internal
variable as Variant, I get the same last two values as your worksheet.
0.9148474335670470
0.3595037460327150
If I declare it as Double, the last two values (loops 9 & 10) are not
even close
0.96567..
0.63005..
I was a little surprised that a Double would throw this off so much.
If I do your worksheet code in a Math program at full precision, the
last two values I get are:
{13664875 / 16777216, 5947841 / 8388608}
Which numerically are:
0.8144900202751160`
0.7090378999710083`
Which is what your vba-rnd procedure returned.
Your observation on this subject is very interesting.
Thanks for the insight. :>)
= = = = =
Dana DeLouis
Dana DeLouis
values using your equation at Machine Precision. However, if we bump
the precision up just a little, then we can do what Rnd does. I wonder
what Excel is actually doing? Very interesting. :>)
Private Sub Workbook_Open()
Dim a, b, k
Dim x
Dim R
a = 1140671485
b = 12820163
k = 2 ^ 24
'// First 50 Rnd calls
For R = 1 To 50
Cells(R, 1) = Rnd
Next R
'// Double as in Workbook
x = CDbl(327680)
For R = 1 To 50
If x < 1 Then x = x * k
x = dMod(x * a + b, k) / k
Cells(R, 2) = x
Next R
'// Higher Precision for vba
x = CDec(327680)
For R = 1 To 50
If x < 1 Then x = x * k
x = dMod(x * a + b, k) / k
Cells(R, 3) = x
Next R
Range("A1:C50").NumberFormat = "0.00000000000000000"
End Sub
>> Excel rnd VBA Rnd
>> 0.7055475115776060 0.7055475115776060
>> 0.5334241390228270 0.5334240198135370
>> 0.5581560134887690 0.5795186161994930
>> 0.5320560932159420 0.2895624637603760
>> 0.7160687446594230 0.3019480109214780
>> 0.0968921184539795 0.7747400999069210
>> 0.4058379530906680 0.0140176415443420
>> 0.3854335546493530 0.7607235908508300
>> 0.9148474335670470 0.8144900202751150
>> 0.3595037460327150 0.7090378999710080
<snip>
= = = = = = =
Dana DeLouis
Dana DeLouis
Function dMod(x, y)
dMod = x - Int(x / y) * y
End Function
> x = dMod(x * a + b, k) / k
= = = = = = =
HTH :>)
Dana DeLouis
Hans Terkelsen
"Joe User" <joeu2004> wrote in message news:OzSNc8Lz...@TK2MSFTNGP04.phx.gbl...
> "Hans Terkelsen" <dk> wrote:
>> The VBA Rnd article gives the full story,
>> it is easy to reconstruct Rnd in the worksheet.
>
> I would be surprised if we can duplicate the VBA Rnd sequence using only
> Excel formulas, if that is what you mean. At least, I don't think it is
> "easy".
>
Hi, Joe.
I'm sorry that I did not see your message before now,
and you probably won't see this either.
But about the Excel (03) worksheet duplication of VBA's Rnd
A1 empty as start seed 0
A2 =MOD(12820163/16777216-179203*A1,1)
Pull A2 down
A:A follows the VBA Rnd values nicely for a hundred cells when I tried that.
In VBA r = Rnd(-1687215) will take one quickly to the 0 random value
16777216 is 2^24
Rnd is only 24 bits, the last half of the bits are 0.
Maybe thats why the formula stays on track.
You could have been unlucky with the start seed where I had 0
Yours could have had more than 24 bits, and then the sequences diverged.
But thats just loose guesswork.
Sorry about being so late, Hans T.