Random Number Generator

[Sophia]

Can someone post a RNG better than the one is provided by Microsoft?

TIA,

 

[Nigel]

Better in what respect ?

 

[Harlan Grove]

Better in what respect ?

Perhaps randomness. The PRNG in XL2003 (after applying service packs and
other updates) is a huge improvement over the PRNG in previous versions in
terms of Diehard performance.

For the OP: there are thousands of websites with open source software
containing variations of the Berkeley PRNG. I'm most familiar with the gawk
source code, and here's a url for it in a web page.

http://wuarchive.wustl.edu/mirrors/NetBSD/NetBSD-current/src/gnu/dist/gawk/random.c

 

[Jerry W. Lewis]

The formulas for the XL2003 generator is given at
http://support.microsoft.com/default.aspx?scid=828795
Fortran code from the original Journal of the Royal Statistical Society,
Series 3 (Applied Statistics) article is available from
http://lib.stat.cmu.edu/apstat/183

Jerry

 

[onedaywhen]

Sophia wrote ...

Can someone post a RNG better than the one is provided by Microsoft?

Does the following qualify?

Microsoft Knowledge Base Article - 44738
Generating a Random Number without RAND() in Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;44738

--

 

[Jerry W. Lewis]

To know whether it is better, you would have to do a number of tests of
randomness in the actual context of use. Particular calculation
operations take a set number of cycles (modified by any other
applications that are sharing the CPU). Clock based RNGs have the
potential to have serious autocorrelation problems.

Jerry

 

[DennisE]

Can an Excel guru answer this?

Knowledge Base Article 44738, states that the expression
(NOW()*100000 - INT(NOW()*100000))
can be used as an alternative to the RAND() function for generating random
numbers. My question is, when Excel evaluates the above formula in any one
instance, is the value of the NOW() function on the left going to be the same
as that on the right, or are they going to be different cause they are each
evaluated a microsecond apart in time?

-- Dennis Eisen

 

[onedaywhen]

(e-mail address removed) wrote ...

Can an Excel guru answer this?

Knowledge Base Article 44738, states that the expression
(NOW()*100000 - INT(NOW()*100000))
can be used as an alternative to the RAND() function for generating random
numbers. My question is, when Excel evaluates the above formula in any one
instance, is the value of the NOW() function on the left going to be the same
as that on the right, or are they going to be different cause they are each
evaluated a microsecond apart in time?

In lieu of a guru, I'll have a stab.

If you use the same formula in different cells you get exactly the
same formula. So I conclude the formula uses the same value for NOW().

--

 

[Jerry W. Lewis]

(e-mail address removed) wrote ...



In lieu of a guru, I'll have a stab.

If you use the same formula in different cells you get exactly the
same formula. So I conclude the formula uses the same value for NOW().

Not true. NOW() only resolves down to the seconds level, which is a
very long time span in computer terms. You have to have a lot of
recalculation going on to see the difference, but =NOW() in different
cells will return different times if there is enough calculation taking
place between the two cell evaluations.

The following is conjecture, but I would be surprised if it is not accurate.

I would expect that each call to NOW() is individually evaluated, so
using NOW() twice in a cell has the potential to return different
values. In practice, I would expect that occurrence to be extremely rare.

I would expect that the final evaluation of a cell (Excel may start
evaluating it, and realize that there are precedents that must be
evaluated first, go do them, and come back) would take place in its
entirety, before moving on to the next cell in the order of evaluation.
Thus you would have to be extremely close to crossing a second
boundary to observe a difference in the two NOW() calls of
(NOW()*100000 - INT(NOW()*100000))

Jerry
Excel MVP