A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to get repeatable Excel RAND sequence?



 
 
Thread Tools Display Modes
  #1  
Old March 21st, 2010, 11:01 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

Is there any way to seed (initialize) the Excel 2003 RAND function so that
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).

Ads
  #2  
Old March 21st, 2010, 01:00 PM posted to microsoft.public.excel.worksheet.functions
Charles Williams
external usenet poster
 
Posts: 235
Default How to get repeatable Excel RAND sequence?


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.

Is there any way to seed (initialize) the Excel 2003 RAND function so that
subsequent calls to RAND result in a repeatable sequence?

  #3  
Old March 21st, 2010, 03:26 PM posted to microsoft.public.excel.worksheet.functions
Charles Williams
external usenet poster
 
Posts: 235
Default How to get repeatable Excel RAND sequence?

On rereading your post I see you have already referenced KB828795 ...


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.

Is there any way to seed (initialize) the Excel 2003 RAND function so that
subsequent calls to RAND result in a repeatable sequence?

  #4  
Old March 21st, 2010, 03:41 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

"Charles Williams" wrote:
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" wrote in message
...

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.

Is there any way to seed (initialize) the Excel 2003 RAND function so that
subsequent calls to RAND result in a repeatable sequence?



----- previous message -----

"Joe User" joeu2004 wrote in message
...
Is there any way to seed (initialize) the Excel 2003 RAND function so that
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).


  #5  
Old March 21st, 2010, 08:45 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

"Joe User" joeu2004 wrote:
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
...
"Charles Williams" wrote:
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" wrote in message
...

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.

Is there any way to seed (initialize) the Excel 2003 RAND function so
that
subsequent calls to RAND result in a repeatable sequence?



----- previous message -----

"Joe User" joeu2004 wrote in message
...
Is there any way to seed (initialize) the Excel 2003 RAND function so
that
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).



  #6  
Old March 23rd, 2010, 12:02 PM posted to microsoft.public.excel.worksheet.functions
Hans Terkelsen
external usenet poster
 
Posts: 28
Default How to get repeatable Excel RAND sequence?


"Joe User" joeu2004 wrote in message ...
"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.

  #7  
Old March 26th, 2010, 09:34 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

"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".

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 D716 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 = [email protected]
Const a As Currency = [email protected]
Const c As Currency = [email protected]
Static x1 As cType, notFirst As Integer
Dim lx1 As l64Type
If notFirst = 0 Then x1.c = [email protected]: notFirst = 1
x1.c = [email protected] * x1.c * a + c
'effectively: x1 = x1 mod [email protected]
'alternative: x1 = x1 - [email protected] * Int(x1 / [email protected])
LSet lx1 = x1
lx1.msb = 0: lx1.lsb = lx1.lsb And &HFFFFFF
LSet x1 = lx1
myRnd = CSng(x1.c * [email protected]) / 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
...

"Joe User" joeu2004 wrote in message
...
"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.


  #8  
Old March 26th, 2010, 10:10 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

"Hans Terkelsen" dk wrote:
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
...

"Joe User" joeu2004 wrote in message
...
"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.


  #9  
Old March 26th, 2010, 03:24 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default How to get repeatable Excel RAND sequence?

I wrote:
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
...
"Hans Terkelsen" dk wrote:
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
...

"Joe User" joeu2004 wrote in message
...
"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.



  #10  
Old March 26th, 2010, 05:18 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default How to get repeatable Excel RAND sequence?

Hi. Your code is very interesting. I can't add much, but here's an
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 a


{13664875 / 16777216, 5947841 / 8388608}

Which numerically a

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



On 3/26/10 4:34 AM, Joe User wrote:
"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".

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 D716 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 = [email protected]
Const a As Currency = [email protected]
Const c As Currency = [email protected]
Static x1 As cType, notFirst As Integer
Dim lx1 As l64Type
If notFirst = 0 Then x1.c = [email protected]: notFirst = 1
x1.c = [email protected] * x1.c * a + c
'effectively: x1 = x1 mod [email protected]
'alternative: x1 = x1 - [email protected] * Int(x1 / [email protected])
LSet lx1 = x1
lx1.msb = 0: lx1.lsb = lx1.lsb And &HFFFFFF
LSet x1 = lx1
myRnd = CSng(x1.c * [email protected]) / 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
...

"Joe User" joeu2004 wrote in message
...
"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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.