[math-fun] Microsoft Excel : really bad random number generator
Hello, I am conducting some tests with the built-in random number generator of Excel. I asked 4 runs of 30,000 random numbers between 0 and 1. It has some limitations of course since a column can hardly have more rows anyway, if you ask for 1 million entries the program is too dumb to figure out to spread the results on many columns, so you have to split the thing in many parts. In principle this works since it apparently make a different run each time BUT...! There is a little surprise with this. Actually it does not look really a good random number generator because of this. - There are 120,000 entries but only 3037 are different, in other words it did pick up the same number up to 15 times! - The precision is suppossedly of 15 digits but according to the choice of different values I bet one could find the generator by doing a reverse engineering on it, ;-) - It did pick up the number 1 , 2 times. I think that this is BAD, how a random number generator can pick up the same values so many times at a precision of 16 digits : this is impossible. Did someone ever tested this particular bad program and if so, does anybody has any idea what is the formula or algorithm they (Bill Gates and Co.) use? I suspected that Excel and Microsoft are not so advanced , I had no idea it was so bad! I use the latest version of Excel with Windows XP, service pack 1 and Office XP. If you want to do a simulation and you use Excel? : bad idea. ps: I should send this to D.E. Knuth! Simon Plouffe
Very interesting! This could conceivably be due to (software) bit-rot -- i.e., the tendency of programs to accumulate bugs over many generations of hardware ports and bug "fixes". I would imagine that the type of testing used by Microsoft is appropriate for a lot of things, but not for random number generators. Perhaps they should define the random number generator with an exact bit output -- then they could at least test it using standard testing tools. This should now be possible due to IEEE standard arithmentic (if they use floating point). At 12:37 PM 6/10/2004, Simon Plouffe wrote:
Hello,
I am conducting some tests with the built-in random number generator of Excel.
I asked 4 runs of 30,000 random numbers between 0 and 1.
It has some limitations of course since a column can hardly have more rows anyway, if you ask for 1 million entries the program is too dumb to figure out to spread the results on many columns, so you have to split the thing in many parts. In principle this works since it apparently make a different run each time BUT...!
There is a little surprise with this.
Actually it does not look really a good random number generator because of this.
- There are 120,000 entries but only 3037 are different, in other words it did pick up the same number up to 15 times! - The precision is suppossedly of 15 digits but according to the choice of different values I bet one could find the generator by doing a reverse engineering on it, ;-) - It did pick up the number 1 , 2 times.
I think that this is BAD, how a random number generator can pick up the same values so many times at a precision of 16 digits : this is impossible.
Did someone ever tested this particular bad program and if so, does anybody has any idea what is the formula or algorithm they (Bill Gates and Co.) use?
I suspected that Excel and Microsoft are not so advanced , I had no idea it was so bad!
I use the latest version of Excel with Windows XP, service pack 1 and Office XP.
If you want to do a simulation and you use Excel? : bad idea.
ps: I should send this to D.E. Knuth!
Simon Plouffe
On Jun 10, 2004, at 3:37 PM, Simon Plouffe wrote:
I am conducting some tests with the built-in random number generator of Excel.
According to http://support.microsoft.com/default.aspx?scid=kb;en-us;86523 it iterates r -> fractional part of (9821 * r + 0.211327) starting with a seed of r=.5. That page claims that "This formula will provide up to 1 million different numbers," which doesn't seem to hold up to your only seeing 3037 distinct ones.
- It did pick up the number 1 , 2 times.
Hmm, so your sequence is not cyclic?! This is bizarre. Unless the "equals" function is somehow not very sensitive? If excel reports a=b, does it necessarily mean that a-b=0? --Michael Kleber kleber@brandeis.edu
Also according to that support page, 'adding randomize=1 .... will cause "r" to be determined from the system clock (which adds a further degree of randomness to the numbers generated)'. And further down it has a NOTE: Excel 5.0 defaults to the RANDOMIZE=1 setting. Previous version of Microsoft Excel defaulted to RANDOMIZE=0. So, unless you have evidence that the formula is being followed (which should be clear from any two successive generated numbers), I'd guess that the PNG isn't being used. --ms Michael Kleber wrote:
On Jun 10, 2004, at 3:37 PM, Simon Plouffe wrote:
I am conducting some tests with the built-in random number generator of Excel.
According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;86523
it iterates r -> fractional part of (9821 * r + 0.211327) starting with a seed of r=.5. That page claims that "This formula will provide up to 1 million different numbers," which doesn't seem to hold up to your only seeing 3037 distinct ones.
- It did pick up the number 1 , 2 times.
Hmm, so your sequence is not cyclic?! This is bizarre. Unless the "equals" function is somehow not very sensitive? If excel reports a=b, does it necessarily mean that a-b=0?
--Michael Kleber kleber@brandeis.edu
_______________________________________________ math-fun mailing list math-fun@mailman.xmission.com http://mailman.xmission.com/cgi-bin/mailman/listinfo/math-fun
No surprise there. A similar thing happened to Gary Demos back at triple-i (ca 1975). He was making pictures of simulated galaxys, using fortran's random number generator, and kept getting really clumpy pictures with lines of stars. Later, with a better random number source, the same algorithm produced very nice galaxys. Rendering the random number sequence graphically really made the shortcomings visible.
participants (5)
-
Dave Dyer -
Henry Baker -
Michael Kleber -
Mike Speciner -
Simon Plouffe