 |
 |
There seemed to be a bug in excel97 on some machines which is maybe worth keeping an eye out for. Generating 250,000 random normal(0,1) numbers produced The occasional value of 5,000,000, next largest value was about 4. I can't remember the exact details, I could dig them out if anyone is interested. Frances Provan Edinburgh University Computing Services > Date: Tue, 31 Aug 1999 15:36:48 +0100 > From: [log in to unmask] > To: [log in to unmask] > Subject: Random Numbers in Excel > Message-Id: <[log in to unmask]> > > As part of a consultancy project I have been helping a customer obtain > random samples > from a database. My client uses Microsoft Office products extensively and it > would > have been convenient to use the random number generator RAND() in Excel to > create the samples. Before doing so I decided to to first check out the > random number > generator. According to the Excel website the random number generator is > given by the > following expression: > > The first random number: > > random_number = fractional part of (9821 * r + 0.211327) > > where r = 0.5 > > Successive random numbers: > > random_number = fractional part of (9821 * r + 0.211327) > > where r = the previous random number. > > I have looked in the following books but am unable to find any theoretical > basis for such > a random number generator. > > Stochastic Simulation, BD Ripley > Elements of Simulation, BJT Morgan > The Art of Computer Programming, Vol 2, Seminumerical Algorithms, D Knuth > Numerical Recipes in FORTRAN, Press et al. > > In the absence of a theoretical basis I advised my client not to use the > Excel random > number generator but instead to use SPSS to generate the random numbers. I > then wrote a Java program to create 4 million random numbers from the above > algorithm and submitted > them to the DIEHARD random number tester from George Marsaglia of Florida > State University. > > The Excel random number generator failed all 15 tests pretty badly. > > Given the widespread availability of Excel (much more than RANDU, for > example) I'd like > to draw attention to this problem. > > Blaise F Egan > Data Mining Consultant > BT Data Mining Consultancy > PP 34 / Room 161 / B81 > BT Labs, > Martlesham Heath > Ipswich > IP5 3RE > Tel: 01473 649818 > Fax: 01473 640897 > Email: [log in to unmask] > www: http://homepages.gold.ac.uk/blaise-f-egan/ %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%