Pseudo-random numbers

Computer simulations of real-life phenomena are built using values that are randomly generated from distributions. For example, a board game might be simulated using random rolls of a die by successive players, with each roll being a randomly generated value from a \(\UniformDistn(1,6)\) distribution.

Values may be randomly generated using a physical mechanism (such as rolling a 6-sided die) but they are usually generated on a computer with some algorithm. Strictly speaking, these should be called pseudo-random values but, if a good enough algorithm is used to generate them, they are practically indistinguishable and 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)

Example: Generating values from a normal distribution

The diagram below illustrates this method for generating values from a normal distribution with mean 2.0 and standard deviation 0.5.

Click Next Value a few times to see how a simulated value from a \(\RectDistn(0,\; 1)\) distribution on the vertical axis leads to a simulated value from the target \(\NormalDistn(\mu = 2.0,\; \sigma = 0.5)\) distribution.

Click Accumulate then generate a few more values. The jittered dot plot on the vertical axis shows the \(\RectDistn(0,\; 1)\) values, whereas the jittered dot plot on the horizontal axis illustrates that the distribution of the generated values is indeed the target normal distribution.

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.

Click anywhere in the CDF to see how rectangularly distributed values are translated into values from this Poisson distribution.