This article discusses various aspects of generating uniform and non-uniform random numbers in Excel worksheets, including the standard Excel approach, its limitations, and an alternative solution.
Uniform Random Numbers - The Standard Excel Way
Uniform Random Numbers - Using EasyFitXL
Non-Uniform Random Numbers - The Standard Excel Way
Non-Uniform Random Numbers - Using EasyFitXL
Visual Random Number Generation
The increasing popularity of Monte Carlo simulation methods and Microsoft Excel as a business data analysis and simulation platform resulted in a great demand for reliable and fast random number generation functions for many probability distributions. However, the standard Excel installation provides only a basic support for statistical functions, limiting the range of applications to very simple simulations.
EasyFitXL is an Excel add-in that helps you overcome the limitations of Excel and easily generate random numbers from more than 40 distributions in your worksheets and VBA applications. It provides a number of fast, well-tested functions enabling you to create Excel-based probability models and perform Monte Carlo simulation studies.
The standard Excel RAND function can be used to generate random numbers from the Uniform distribution, however, be aware that in Excel 2002 and earlier versions, this function should not be used with large simulation models. The older versions of Excel use the generation algorithm which has a relatively small period (less than 1 million numbers), so if your model contains hundreds of variables and you are running the simulation tens of thousands of times, you can run out of random numbers. This problem has been fixed in Excel 2003 and later versions.
In addition, if you use Excel 2003, note that there is a bug in this version causing the RAND function to return negative numbers, so you should make sure you have an appropriate hotfix package installed that solves the problem.
The RAND function returns numbers from the interval [0,1), and if you need to generate numbers from another interval, you should use the following formula:
=RAND() * (b−a) + a
This will return random numbers from the interval [a,b) - greater than or equal to a, and less than b.
To address the quality and usability issues of the standard RAND function, EasyFitXL provides the UniformRand function which performs equally well under all Excel versions starting from Excel 2000 and higher. This function is based on the Mersenne Twister random number generation algorithm considered to be an industry standard for high-performance simulation. The period of this generator is more than 106000, which is more than enough for all imaginable applications. The random sequences generated using this method are of a very high quality: the generator passes numerous tests for statistical randomness, including the well-known Diehard tests (a number of statistical tests for measuring the quality of a set of random numbers).
The UniformRand function accepts two parameters, a and b, and returns uniformly distributed random numbers from the interval [a,b), so you don't need to use the above mentioned formula. If you need to mimic the behaviour of the RAND function, you can use UniformRand(0;1) in your worksheets.
In addition, EasyFitXL provides the DUniformRand function which can be used to generate integer random numbers. This function works in the same way as the standard RANDBETWEEN function, however, it is also based on the Mersenne Twister method, and overcomes the issues of the older Excel versions.
See Part II: How To Generate Non-Uniform Random Numbers in Excel