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 |
|
#1
|
|||
|
|||
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). |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 = 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. |
#8
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|