what define the random seed within Excel?

http://support.microsoft.com/kb/828795    <=== doesn't tell how the seed is generated.

http://social.answers.microsoft.com/Forums/en-US/excelform/thread/38791dbb-0695-4be7-a3d1-14b0864d3182

fron what I could find, the rand() function can't be seeded and have seen no documentation about how the seed is generated.

---

--
Tom Ogilvy
note: If you receive an answer to your question - please mark that answer or answers so others know the question has been answered.

oem7110 wrote:

When using Rand() function to generate random number based on a random seed, does anyone have any suggestions on how excel defines the random seed?
Does it define based on Time or other approach?

See my comments in the discussion at http://social.answers.microsoft.com/Forums/en-US/excelform/thread/38791dbb-0695-4be7-a3d1-14b0864d3182.

I believe I did find MS documentation that states that RAND is seeded using "system time".  But that is ambiguous; in MSWin, there are several different representation of "system time" with varying degrees of precision.

Also I believe I found MS documentation that indicated that previous versions of Excel did provide a mechanism for controlling the seed for RAND.  But that mechanism no longer works.  And no one was able to offer an alternative mechanism when I asked some time ago.

oem7110 wrote:

Does anyone have any suggestions about the general concept about random seed within Excel

As I wrote in the aforementioned discussion, you could roll your own Wichman-Hill algorithm in VBA, seeding as you please.  See http://support.microsoft.com/kb/828795.  In fact, you could implement a better PRNG algorithm, if you have one.

Alternatively, you can use the VBA Randomize statement and Rnd function.  However, the Rnd algorithm is different from RAND, and the period (before repeating the sequence) is shorter.

Does anyone know whether Fortran coding "AMOD" is equal to Excel coding "MOD" or not?

But referring to article http://support.microsoft.com/kb/828795, Fortran coding "MOD" is existed, so "AMOD" must be something different,

does anyone have any suggestions what function within Excel performs the same what "AMOD" does in Fortran language?

 

---

Thank everyone very much for any suggestions

In order to generate the random numbers, I need to select 3 numbers for IX, IY, IZ, does this number usually retrieve from current time? How do they retrieve this numbers from TIME? IX from hour, IY from minute, IZ from second?

Based on following description, if I would like to generate a random number from 1 to 60, does anyone have any suggestions on how to convert the generated float number from this random seeds into any number within range from 1 to 60? Is there any general approach to do that?

Furthermore, can I change the divisor into different number for difference? or is there any reason that I need to keep using this numbers (30269,30307,30323,171,172,170)?

=======================================================================================================

The basic idea is that if you take three random numbers on [0,1] and sum them, the fractional part of the sum is itself a random number on [0,1]. The critical statements in the Fortran code listing from the original Wichman and Hill article are:

Therefore IX, IY, IZ generate integers between 0 and 30268, 0 and 30306, and 0 and 30322 respectively. These are combined in the last statement to implement the simple principle that was expressed earlier: if you take three random numbers on [0,1] and sum them, the fractional part of the sum is itself a random number on [0,1].

---

Thank everyone very much for any suggestions

oem7110 wrote:

Does anyone know whether Fortran coding "AMOD" is equal to Excel coding "MOD" or not?

Excel MOD?!   Yes.   But not VBA MOD.

Here is a VBA implementation....

Public Declare Function QueryPerformanceCounter _
    Lib "kernel32" (ByRef cnt As Currency) As Boolean

Function myWH() As Double
Static ix As Long, iy As Long, iz As Long
Dim r As Double, sc As Currency
If ix = 0 Or iy = 0 Or iz = 0 Then
   'seed PRNG
   QueryPerformanceCounter sc: r = sc * 10000
   ix = r - 30269 * Int(r / 30269): If ix = 0 Then ix = 1
   r = r / 101  'arbitrary prime divisor
   iy = r - 30307 * Int(r / 30307): If iy = 0 Then iy = 1
   r = r / 101  'arbitrary prime divisor
   iz = r - 30323 * Int(r / 30323): If iz = 0 Then iz = 1
End If
ix = (171 * ix) Mod 30269
iy = (172 * iy) Mod 30307
iz = (170 * iz) Mod 30323
r = ix / 30269 + iy / 30307 + iz / 30323
myWH = r - Int(r)
End Function

QueryPerformanceCounter provides a very precise measure of system time since start-up -- each unit represents about 279.365 nanosec on my computer.  YMMV.  If you do not want to use QueryPerformanceCounter, I could offer a reasonable alternative.

oem7110 wrote:

In order to generate the random numbers, I need to select 3 numbers for IX, IY, IZ, does this number usually retrieve from current time?  How do they retrieve this numbers from TIME?  IX from hour, IY from minute, IZ from second?

Same answer as before: no one knows, to my knowledge.  However, I have a lot of experience with PRNGs, and I can say it would be very poor to initialize IX, IY and IZ as you describe.

oem7110 wrote (quoting http://support.microsoft.com/kb/828795):

C IX, IY, IZ SHOULD BE SET TO INTEGER VALUES BETWEEN 1 AND 30000 BEFORE FIRST ENTRY

That comment is incorrect.  I tested the Wichman-Hill algorithm with all valid starting IX, IY and IZ values, where "valid" is less than the individual modulo divisors (30269, 30307, 30323).  Any starting values other than zero will generate the complete sequence (much more than 10^13 numbers).

oem7110 wrote:

Furthermore, can I change the divisor into different number for difference? or is there any reason that I need to keep using this numbers (30269,30307,30323,171,172,170)?

Don't alter the values.  The design of PRNGs -- in this case, the choice of divisor -- must be done very carefully.  Wichman and Hill chose these divisors, not MS.  Experts have quibbled with this version of the Wichman-Hill algorithm.  Wichman and Hill did offer an "improved" algorithm.  But experts still quibble with it.  That is why MS switched to a Mersenne Twister algorithm starting in XL2010.

Nevertheless, I'm sure the Wichman-Hill algorithm in KB 828795 will be good enough.

oem7110 wrote:

Based on following description, if I would like to generate a random number from 1 to 60, does anyone have any suggestions on how to convert the generated float number from this random seeds into any number within range from 1 to 60?  Is there any general approach to do that?

Same as you would do with RAND, to wit:  INT(1+60*RAND()).

I think it is better to implement Wichman-Hill in VBA, not Excel.  But if you want to implement it in Excel, post your formulas, and I will help you make it work for you, if possible.

Errata....  I wrote in a previous response:

I believe I did find MS documentation that states that RAND is seeded using "system time". But that is ambiguous; in MSWin, there are several different representation of "system time" with varying degrees of precision.
 
Also I believe I found MS documentation that indicated that previous versions of Excel did provide a mechanism for controlling the seed for RAND. But that mechanism no longer works.

I did a lot of work a year or two ago researching all this very thoroughly.  But I must admit:  my memory of it is a little fuzzy; I'm not taking the time to find all of my notes (some of which exist only in newsgroups); and, frankly, it's late.  Sigh.

I do now have a better recollection of the "MS documentation that states that RAND is seeded using system time".  That comes from the VBA help page for Randomize, which of course applies to VBA Rnd, not necessarily Excel RAND.  Ironically, I used that fact in the past -- and the fact that I cannot find any MS documentation about how RAND per se is seeded -- to question other people's unqualified assertions about how RAND is seeded.  Klunk!

Nevertheless, it is a good bet that if Rnd is seeded using "the system timer", so is RAND.  A "good bet", but by no means a certainty.  However, even if that were true, that still is not sufficient information because, as I said, there are many representations of "the system timer" in MSWin; and there are an uncountable number of ways that system time could be used to initialize the individual factors in the Wichman-Hill algorithm.

I wrote (part of VBA implementation of the Wichman-Hill algorithm):

If ix = 0 Or iy = 0 Or iz = 0 Then
   'seed PRNG
   QueryPerformanceCounter sc: r = sc * 10000
   ix = r - 30269 * Int(r / 30269): If ix = 0 Then ix = 1
   r = r / 101  'arbitrary prime divisor
   iy = r - 30307 * Int(r / 30307): If iy = 0 Then iy = 1
   r = r / 101  'arbitrary prime divisor
   iz = r - 30323 * Int(r / 30323): If iz = 0 Then iz = 1
End If

I did that long ago to demonstrate one (arbitrary) way in which "system time" could be used.

But for practical purposes, there is a much simpler way to initialize IX, IY and IZ.  To wit....

If ix = 0 Or iy = 0 Or iz = 0 Then
   Static first As Long
   If first = 0 Then Randomize: first = 1
   ix = Int(1 + 30268 * Rnd)
   iy = Int(1 + 30306 * Rnd)
   iz = Int(1 + 30322 * Rnd)
End If

There is another way to create a random number sequence entirely within Excel, that uses the
Lewis-Goodman-Miller  algorithm  "Lewis, P. A. W., A. S. Goodman, and J. M. Miller,
1969. A pseudo-random number generator for the system/360. IBM System's Journal 8:136-143."

This codes up the Lehmer linear congruential formula, and put it into each cell where a random number is needed.  A nice description of the method --- a.k.a the "Park and Miller Minimum standard generator" appears in "Park SK, Miller KW (1988). Random Number Generators: Good Ones are Hard to Find;  Communications of the ACM, pp. 1192-1201."   It is certainly not fancy enough for cryptography or secure message services, but works well enough if you need only a few thousand or a few million random numbers for example to deviate spreadsheet predictions with noise.

My Excel code for this one-cell routine is described a short writeup at www.MikeLampton.com, entitled
Random Numbers for Spreadsheets.

Mike wrote:

My Excel code for this one-cell routine is described a short writeup at www.MikeLampton.com, entitled Random Numbers for Spreadsheets.

Although this has the appearance of being published in the J of Stat Software, I notice that the issue and volume information are boilerplated.  Where are you in the submission/acceptance process?

There are some details that are incorrect.  It would be nice to correct them before publication.

I have not completed my "review".  But for example:


1. IEEE-754 64-bit floating-point can accurately represent integers in the range of -2^53 to +2^-53, not -2^52 to +2^52-1 as the article states.

And in any case, the range is completely symmetrical:  -2^x to +2^x, not -2^x to +2^x-1


2. "Spreadsheets" (Excel at least) do not "typically deliver exact integer arithmetic only for numbers whose size is less than about 10^15, i.e. fifteen digits accuracy".

That is an oft-repeated fallacy even in Microsoft online documentation.


You can validate both corrections with the following simple experiment:

1. In A1, enter the formula =2^53.  (Alternatively, enter =-2^53.)
2. In A2, enter the formula =SUM(A1,-TEXT(A1,"0.00000000000000E+0")

Note that Excel does display A1 only up to 15 significant digits (formatted as Number), namely 9007199254740990.  But the formula in A2 returns 2.  That demonstrates that the value in A1 is indeed 9007199254740992, the exact value of 2^53.

With respect to point #2, we get into the semantics of terms like "typical" and "deliver".

For example, if the formula in A2 were =A1-TEXT(A1,"0.00000000000000E+0"), the result would indeed be zero(!).  Then again, if A2 were =A1-TEXT(A1,"0.00000000000000E+0")-0, the result would be 2.  (The -0 represents any subexpression whose result is zero.)

That inconsistency is due to the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" at http://support.microsoft.com/kb/78113.  I say "dubious", in part, for the very reason that is applied inconsistently in Excel.

It is true that Excel inputs and displays only up to 15 significant digits.  For the same reason, an expression evaluated in VBA macro (Sub), then stored into an Excel cell is limited to 15 significant digits.  For example, Range("A3")=2^53 does indeed result in exactly 9007199254740990, not 9007199254740992.

(But a VBA Function returns the exact 64-bit floating-point value, not limited to 15 significant digits.)

Also, if the calculation option "Precision as displayed" is set (which you should never do for scientific calculations) and a cell is formatted as General, the cell value is rounded to 15 significant digits.

But generally, arithmetic calculations are carried out to the full precision supported by IEEE-754 64-bit floating-point.  In fact, that is the most-common reason why MATCH and other look-up operations fail to behave as expected.  It is also why IF(10.1-10=0.1,TRUE) returns FALSE(!).

Thank you joeu2004 for all the help & corrections!

Yes at one point I submitted a draft to JSS and therefore adopted their
Header style.  However I have since withdrawn it (inappropriate, really)
and uploaded a shorter version to my website as a Research Note, with
no journal header or format.

My confusion about the displayed inaccuracy of Excel surely stems from
its propensity to round things at about 15 digits, and yes I have surely
confused "delivered" (visible representation) accuracy with internal representation.
It is as if the numbers in Excel's memory are not those shown to its users.
I suppose this rounding is to avoid 0.3 + 0.3 + 0.4 = 0.999999999999.

Thanks again,
Mike Lampton