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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
How to get repeatable Excel RAND sequence?
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 |
#13
|
|||
|
|||
How to get repeatable Excel RAND sequence?
"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. |
|
Thread Tools | |
Display Modes | |
|
|