Distribution Fitting Articles | Software

How To Generate Random Numbers In Excel Worksheets

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.

Contents
Introduction
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
Conclusion

Introduction

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.

Uniform Random Numbers - The Standard Excel Way

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.

Uniform Random Numbers - Using EasyFitXL

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 10⁶⁰⁰⁰, 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.

Non-Uniform Random Numbers - The Standard Excel Way

Even though the RAND function can be useful for generating Uniform random numbers, most of the time you will need to model various non-uniform distributions, such as the Normal, Lognormal, Exponential, Gamma, and others. In fact, the Uniform distribution arises in a very limited number of applications, so the chances are your worksheet models will rarely deal with this distribution.

Excel doesn't provide any functions for directly generating random numbers from the popular continuous and discrete distributions, however, you can use the standard capabilities of Excel and the inverse transform method to create such a function yourself.

How To Use The Inverse Transform Method

In theory, the inverse transform method is very easy to use: to generate a random number from any probability distribution with cumulative distribution function F(x), you need to take the following steps:

• generate a random number from the Uniform distribution: u=Uniform(0,1), and
• calculate x=F^-1(u),

where F^-1(u) is the inverse cumulative distribution function (ICDF) of the same distribution, and x is the resulting random number. To implement this method in Excel, you can use the RAND function to generate random numbers from the Uniform distribution, and apply the built-in functions to calculate the ICDF.

The standard Excel package allows you to calculate the ICDF for a limited number of probability distributions: Beta (the function name is BETAINV), Chi-Squared (CHIINV), F distribution (FINV), Gamma (GAMMAINV), Lognormal (LOGINV), Normal (NORMINV), and Student's t (TINV). For example, the following formula will return the inverse CDF of the Normal distribution with mean=1 and standard_deviation=2 evaluated at p=0.2:

=NORMINV(0.2; 1; 2)

Replacing 0.2 with RAND will yield the Normal random number generation formula:

=NORMINV(RAND(); 1; 2)

Similarly, you can generate random numbers from the other distributions available in Excel. If the distribution you need to use in your worksheet models is not supported, you can create the inverse CDF formula yourself, based on the analytic expression for the cumulative distribution function. For example, the Exponential distribution CDF is defined as:

F(x) = 1 − exp(−λ * x),

and the inverse CDF is:

F^-1(u) = −ln(1−u) / λ,

so, for example, if the value of the λ parameter is 0.5, the resulting Excel generation formula will be as follows:

=−LN(1−RAND()) / 0.5

Limitations Of The Inverse Transform Method

Despite the fact that the inverse transform method can be used to generate random numbers from some distributions, this approach has a number of limitations. The main problem is that this method is based on the inverse cumulative distribution function, which is not well defined for all distributions:

• the inverse transform method only works with continuous distributions, and cannot be used with discrete distributions such as the Poisson, Binomial, Geometric, and Hypergeometric models;
• for some continuous distributions, the inverse CDF is not available in closed form, and cannot be easily calculated in Excel;
• the standard Excel functions BETAINV, CHIINV, GAMMAINV, LOGINV, NORMINV etc. are all based on the approximation formulas, which usually results in accuracy issues (and low-quality random numbers).

The inverse transform method uses Uniform random numbers, so the limitations of the RAND function come into play as well. And finally, to successfully apply this method, you have to derive and use the analytic expressions for the inverse CDF of the distributions you are dealing with, which is a very time-consuming and error-prone process.

Along with the inverse transform method, there are a number of algorithms that can be used to generate random numbers from specific non-uniform distributions, however, these methods cannot be implemented in Excel worksheets due to their relative complexity.

Non-Uniform Random Numbers - Using EasyFitXL

EasyFitXL allows you to easily generate random numbers from more than 40 popular continuous and discrete distributions, ensuring that you never face the challenges of the standard Excel approach. For each distribution, EasyFitXL provides a separate worksheet function based on the generation algorithm optimized for that particular distribution.

The functions are optimized for performance and can be used with any Excel version starting with Excel 2000, including the latest version. EasyFitXL supports the multi-threaded worksheet recalculation feature of Excel 2007, significantly decreasing the calculation times on multi-core computers, enabling you to save time when running large simulations.

EasyFitXL makes it easy for you to insert an appropriate function into your worksheet using the standard Excel Insert Function dialog. The random number generation functions are grouped into a separate category called "EasyFitXL (Random Numbers)":

Once you select a distribution, Excel will ask you to specify the distribution parameters:

For example, using NormalRand(2; 1) in your worksheets will generate high-quality random numbers from the Normal distribution with parameters σ=2 (standard deviation, or scale parameter) and μ=1 (mean, or location parameter). For this particular distribution, EasyFitXL uses the generation method based on the Box-Muller transform, which is considered to be one of the best methods for generating Normal random numbers in terms of quality and performance, in contrast to the inverse transform method.

You can generate random numbers from most non-uniform distributions in a similar way:

• commonly used continuous distributions: Beta, Chi-Squared, Exponential, Gamma, Laplace, Lognormal, Normal, Pareto, Pert, Triangular, Weibull, and others;
• less frequently applied but still very useful continuous models: Cauchy, Extreme Value (Gumbel and Frechet), Inverse Gaussian, Johnson's distributions (SB and SU models), Logistic, Log-Logistic, Power Function, Rayleigh, and Fatigue Life (Birnbaum-Saunders);
• advanced continuous distributions: Generalized Extreme Value (GEV), Generalized Pareto, Wakeby etc.;
• discrete distributions: Poisson, Binomial, Negative Binomial, Geometric, Hypergeometric, and Logarithmic.

Visual Random Number Generation

The standard Excel behaviour is to automatically update all the cells containing random numbers when the worksheet is recalculated. When performing Monte Carlo simulations, this feature is used to recalculate the entire model thousands of times, each time with a new set of random numbers.

However, if you need to generate a single set of random numbers from a particular distribution and store the numbers in a current sheet or another location, using worksheet functions is not the best idea. EasyFitXL offers an easy solution to this problem: select "Random Numbers" from the EasyFitXL menu:

In the dialog that appears, select a distribution and specify its parameters. You can also specify the Sample Size (how many random numbers to generate) and Destination (where the numbers should be stored):

The Random Seed option can be used to initialize (seed) the random number generator in order to reproduce the same sequence of pseudorandom numbers. Specify a positive integer number (1, 2, ...) to produce a specific sequence, or leave the field blank if you don't need this particular feature.

Once you are done, click OK, and EasyFitXL will proceed to generating random numbers. If the Destination is Current Worksheet, the random numbers will appear in the currently selected column:

Conclusion

The standard Excel package includes a basic set of functions which can be used to generate random numbers from a limited number of distributions. You can also extend the functionality of Excel by developing your own formulas based on the inverse transform method, however, this approach is quite time-consuming and error-prone, and does not work for all distributions. To overcome the limitations of Excel, prevent calculation errors, and increase the performance of your worksheet models, you can use the EasyFitXL add-in allowing to easily generate high-quality random numbers from all the commonly used probability distributions.

See also: Generating Random Numbers using Excel (www.vertex42.com)