How to get repeatable Excel RAND sequence?

Hi. As a side note, this is interesting because I can't get vba's Rnd
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

Oops! I forgot to include the aux program dMod()

Function dMod(x, y)
dMod = x - Int(x / y) * y
End Function


x = dMod(x * a + b, k) / k

= = = = = = =
HTH :)
Dana DeLouis

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





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

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