Distribution Fitting Software & Articles

How To Generate Random Numbers In Excel Worksheets - Part II


Contents
Non-Uniform Random Numbers - The Standard Excel Way
Non-Uniform Random Numbers - Using EasyFitXL
Visual Random Number Generation
Conclusion

Non-Uniform Random Numbers - The Standard Excel Way

EasyFitXL – Easily Fit Distributions in Excel

EasyFitXL Sample Screen - Click To Enlarge

• over 55 distributions
• goodness-of-fit tests
• interactive graphs
• random numbers
Download Free Trial

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

EasyFitXL – Easily Fit Distributions in Excel

EasyFitXL Sample Screen - Click To Enlarge

• over 55 distributions
• goodness-of-fit tests
• interactive graphs
• random numbers
Download Free Trial

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 50 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 2010 and 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)":

Insert Function dialog - Excel 2007
 

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

Function Arguments dialog - Excel 2007
 
EasyFitXL – Easily Fit Distributions in Excel

EasyFitXL Sample Screen - Click To Enlarge

• over 55 distributions
• goodness-of-fit tests
• interactive graphs
• random numbers
Download Free Trial

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, Cauchy, Chi-Squared, Exponential, Gamma, Laplace, Lognormal, Normal, Pareto (1st and 2nd kind), Pert, Triangular, Weibull, and others;
  • less frequently applied but still very useful continuous models: Extreme Value (Gumbel and Frechet), Inverse Gaussian, Johnson's distributions (SB and SU models), Levy, Logistic, Log-Logistic, Nakagami, Pearson (type 5 and 6), Power Function, Rayleigh, Rice, and Fatigue Life (Birnbaum-Saunders);
  • advanced continuous distributions: Generalized Extreme Value (GEV), Generalized Pareto, Log-Pearson III (LP3), Wakeby etc.;
  • discrete distributions: Bernoulli, Discrete Uniform, 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:

EasyFitXL menu - Random Numbers
 

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):

Generate Random Numbers dialog
 

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:

Random numbers
 

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:
How To Generate Uniform Random Numbers in Excel (the 1st part of this article)
Generating Random Numbers in Excel (www.vertex42.com)