 |
 |
Oh God! I noticed this value recently when I generated around 100 rann(0,1) in excel but thought that I might have inadvertently edited that value. -- Krishnadas > Please respond to [log in to unmask] > To: [log in to unmask] > cc: > Subject: excel... > > > > 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/ >