RE: Random Numbers

["Mark Bailey" <Mark.Bailey@sas.com>]

Ian,

Yes, they are not true random numbers. They can't be. They are generated by a computer algorithm. Given the same starting point, they always produce the same sequence of outputs. That is why they are called 'pseudo-random.' Usually a random 'seed' is used to initiate the sequence, like the value of the computer system clock at initialization. In fact, they eventually 'cycle' back through the same sequence. The length of the non-repeating sequence is called the 'period.'

Microsoft Excel is generally a poor tool for statistical work unless you only use it as a framework and invest in implementing your own functions and analyses using Visual BASIC macros. (Remember that a spreadsheet program is designed for financial reporting and consolidation, not science and engineering.) The random number generators are, in fact, especially bad, as documented in a series of articles in The American Statistician by B. D. McCullough, Senior Economist, Federal Communications Commission (bmccullo@fcc.gov). He evaluated Excel, every time a new version is released by Microsoft, against several collections of public domain benchmarks for accuracy and performance. Interestingly, the recent versions are getting worse instead of better!

I suggest that you use statistical software for your purpose since vendors stake their future on getting this specialty correct or prepare your own random number generator. This project is not easy because you must invest a lot of effort in researching the proper algorithms and numerical analysis issues. You cannot simply plug a formula into VB and expect it to work properly. (Abstract mathematics and limited (finite) computing resources do not always match up.)

If you decide to 'roll your own,' you find a good implementation of a good pseudo-random number generator. Next, use the output of this function as the input to the inverse cumulative distribution (or 'quantile' function) of the distribution you want to simulate. (Mathematical statistics theory proves that a uniform probability distribution is appropriate for this purpose.) For acceptance sampling, you will likely use the negative binomial and they hyper-geometric distributions.

All of this effort it to ensure that the probabilities that you compute are realistic. That is, as realistic as they can be from the purely statistical issues. This contribution is always a lower bound on the real variation.

-Mark