15. Random Numbers

15.1 Overview

A sequence of numbers that takes random values is called a random number. Random numbers are used in situations such as selecting samples randomly. In numerical computation, examples where random numbers are necessary include the Monte Carlo method and simulation of probabilistic phenomena.

Generating random numbers on a computer means obtaining random numbers using a program based on a certain algorithm. Therefore, they are not completely random and possess some regularity. As a result, they are sometimes referred to as pseudo-random numbers.

The advantages of pseudo-random numbers include the ability to generate them whenever needed and the capacity to completely reproduce results for verification purposes. The term pseudo-random numbers generally refers to random numbers that follow a uniform distribution.

Excel provides standard functionality to generate pseudo-random numbers. In VBA, the functions Rnd and Randomize (initialization) are available, while worksheet functions include RAND, RANDARRAY, and RANDBETWEEN.

15.2 Methods for Generating Random Numbers

15.2.1 Linear Congruential Method

This is a classic and widely-used method for generating pseudo-random numbers.

By selecting an integer initial value \(X_0\) within the range of \(0\) and \(m\), a sequence of integers is generated using the following recurrence relation:
\[
X_{i+1} = (aX_i + c) \space mod \space m
\] Here, \(0 \leq a < m\) and \(0 \leq c < m\). Random number sequences can be generated by appropriately choosing the parameters \(a\), \(c\) and \(m\). The period of the random number sequence and its statistical properties (randomness) are determined by how these parameters are chosen. The same initial value \(X_0\) always produces the same random number sequence. Thus, it can be seen that the sequence repeats at most every \(m\) steps. For this reason, it is preferable to select \(m\) as a sufficiently large value. Additionally, to make the calculation of \(mod \space m\) in the recurrence relation faster by leveraging overflow, \(m = 2^{32}\) is often chosen for a 32-bit word computer. When real-valued random numbers are needed, the generated integer random numbers are converted by dividing by \(m\) through floating-point arithmetic. In the VBA function Rnd, the parameters \(m = 2^{24}\), \(a = 16598013\) and \(c = 12820163\) are used. The period is \(2^{24}\), which is not very long. It returns single-precision floating-point numbers within the range \([0, 1)\) as the function value. As an example, let's experiment using the parameters \(m = 2^{32}\), \(a = 69069\) and \(c = 1\), as recommended in reference [1]. The period will be \(2^{32}\). \[ X_{i+1} = (69069X_i + 1) \space mod \space 2^{32} \] Below is an example VB.NET program.

Class Test

Shared Function TRand(ByRef IX As Integer) As Integer
    IX = 69069 * IX + 1
    TRand = IX
End Function

Public Shared Sub Main(ByVal args() As String)
    Dim IX As Integer, I As Integer
    IX = 13
    For I = 1 To 10
        Console.WriteLine(TRand(IX))
    Next
End Sub

End Class

Note – You need to disable integer overflow checks (compile with ‘vbc -removeintchecks+’). Since the method to achieve this in Excel VBA was unclear, VB.NET was used instead.

When the seed was set to 13, the following random number sequence was obtained.

 
897898
1887374819
-1755994680
784918825
-1813853482
-1145091233
1516383764
-1962284539
-1042831614
-735193445

 
One disadvantage of the linear congruential method is that the lower digits are not random. In the example above, it can be observed that odd and even numbers alternate (the least significant bit alternates between 1 and 0).

However, the randomness of the higher digits is good. Therefore, by using only the upper 24 bits, for example, a better level of randomness can be achieved. Additionally, when converting to real random numbers, the periodicity of the lower bits is not usually a significant issue.

15.2.2 Mersenne Twister (MT)

This is a relatively new random number generator developed around 1996 in Japan. It uses an improved algorithm based on the General Feedback Shift Register (GFSR) method. Detailed explanations and programs are available on the homepage of reference [2].

It is a random number generator with an extremely long period (\(2^{19937} – 1\)) and excellent statistical properties (considered more random).

The Mersenne Twister is adopted in the random number generation module of the programming language Python.

15.3 Method for Generating Random Numbers Using XLPack

Excel’s VBA function Rnd and worksheet function RAND can be used to generate random numbers, which is generally sufficient for most purposes. However, for cases requiring double-precision floating-point numbers or exceptionally long periods with superior randomness, VBA functions of the Mersenne Twister, InitGenrand, GenrandInt32, GenrandInt31, and GenrandReal53, are provided.

InitGenrand performs initialization. Its argument (called the seed) determines the starting point of the random number sequence. GenrandInt32, GenrandInt31, and GenrandReal53 generate pseudo-random numbers as signed 32-bit integers, unsigned 31-bit integers, and 53-bit real numbers in the range [0, 1), respectively.

Example

First, perform initialization using InitGenrand, then call GenrandInt32 to generate ten 32-bit integer random numbers.

An example VBA program is shown below.

Sub Start()
    Dim Seed As Long, I As Long
    Seed = 13
    Call InitGenrand(Seed)
    For I = 1 To 10
        Debug.Print GenrandInt32()
    Next
End Sub

The following results were obtained.

  
-954760878 
-1686456144 
 1020231754 
-603726320 
-754717978 
-459635870 
-147106060 
 769458329 
-117677332 
-1036873798 

Reference