Computer simulations consist of realisations of models for real-life scenarios. These models involve distributions, so simulations require values that are randomly generated from the distributions. These random values are usually generated on a computer with some algorithm. Strictly speaking, these should be called pseudo-random values but will simply be called random values here.

The basis of generating random values from a distribution is usually an algorithm that generates a random value from a \(\RectDistn(0, 1)\) distribution. For example, Excel has a function to generate one such value:

=RAND()

The relationship between a \(\RectDistn(0, 1)\) distribution and one with cumulative distribution function \(F(x)\) can be used to generate a random value from an arbitrary distribution.

Random values from an arbitrary distribution

If \(y\) is a random value from a \(\RectDistn(0, 1)\) distribution, then \(F^{-1}(y)\) is a random value from the distribution with cumulative distribution function \(F(x)\).

Excel has built-in functions to evaluate \(F^{-1}(y)\) for several common distributions, including the following ones.

Distribution \(F(x)\) \(F^{-1}(y)\)
\(\NormalDistn(0, 1)\) =NORM.S.DIST(\(x\), true) =NORM.S.INV(\(y\))
\(\NormalDistn(\mu, \sigma^2)\) =NORM.DIST(\(x\), \(\mu\), \(\sigma\), true) =NORM.INV(\(y\), \(\mu\), \(\sigma\))
\(\GammaDistn(\alpha, \lambda)\) =GAMMA.DIST(\(x\), \(\alpha\), \(\frac 1{\lambda}\), true) =GAMMA.INV(\(y\), \(\alpha\), \(\frac 1{\lambda}\))

For example, a random value from a \(\NormalDistn(\mu = 10, \sigma^2 = 4)\) distribution can be generated in Excel by typing the following into a spreadsheet cell:

=NORM.INV(RAND(), 10, 2)

Generating values from a discrete distribution

Although the methodology above is easiest to explain for continuous random variables, it can also be used to generate random numbers from discrete distributions.

Excel only has a function for the inverse of the binomial distribution's CDF. Typing the following into a spreadsheet cell generates a random value from a binomial distribution:

=BINOM.INV(\(n\), \(\pi\), RAND())

The method can however be applied to other discrete distributions too.

Example: Generating values from a Poisson distribution

The diagram below shows the cumulative distribution function for a \(\PoissonDistn(\lambda = 3)\) distribution — a step function. From a randomly generated \(Y\) with a \(\RectDistn(0, 1)\) distribution, you would read across and down to find a random value from the discrete \(\PoissonDistn(\lambda = 3)\) distribution.