MODEL BUILDER 2.1: How to Implement Uniform Pseudorandom Number Generation in Excel

As was just shown in Figure 2.1, a pseudorandom number can take many forms depending on the distribution assumed. In this Model Builder exercise we will start with the most basic pseudorandom generation on the Excel sheet using the RAND function and show how to create a histogram to demonstrate that the RAND function draws numbers between 0 and 1 from a uniform distribution. Note that the file MB2.0 will be used for this Model Builder exercise, but only one sheet is dedicated to it.

1. Open a new workbook in Excel and save it as MB2_User. With all of the future Model Builder exercises, it is preferable that you create your own version and use the book's website versions only when necessary or to check your work. The reference sheet for this exercise in the MB2.0 file is named “Uniform Pseudorandoms from Sht”.
2. On your version of the Model Builder exercise, in C5 enter the following: “=RAND()”. As stated in Chapter 1, enter only the characters you see between the double apostrophes. Notice that this function does not accept any parameters between the parentheses and that when any item on the sheet or workbook is calculated a new value is shown. The value is a uniform pseudorandom number between 0 and 1. Every time F9 is pressed, if Excel is in manual calculation mode or when a calculation takes place, this pseudorandom number will be regenerated. Copy or drag the function down to C104 to create 100 pseudorandom numbers.
3. Next we are going to create a histogram to visually see the distribution of random numbers. We will create “buckets” or intervals and see how many of the pseudorandom numbers fall within each interval. To do this, enter 0 in E6, .1 in F6, and continue entering values increasing by .1 in each successive cell in row 6 until 1 is reached in O6. If this does not make sense refer to the complete electronic files on the website.
4. There are multiple methods in Excel to count the number of pseudorandom numbers that fall within each bucket. We will take a look at three ways, one of which requires Excel 2007 or later. The first method that is backward compatible with versions of Excel prior to Excel 2007 is to use a formula with two COUNTIF functions. Enter the following formula in F7:

   =COUNTIF($C$5:$C$104,“>”&E6)-COUNTIF($C$5:$C$104,“>”&F6)

   FIGURE 2.1 Three histograms depicting a uniform, normal, and lognormal distribution.

   

   FIGURE 2.2 A histogram in Excel, such as this one, helps summarize large data sets

   

   The COUNTIF function counts the number of cells that meet a certain criteria. The criteria parameter can be any logical statement, but it is tricky to enter. If we want to say greater than a referenced value then we have to put the “greater than” symbol (>) in double quote marks and then use the ampersand to connect the greater than symbol to a referenced cell, which contains a value. The first part of the formula above counts the number of cells that are absolutely greater than the first bucket value (0 in this case). To find out the number of cells that are WITHIN the next bucket range we subtract out the number of cells that are greater than the next interval in the bucket range. Copy and paste this formula over the range F7:O7. Also, we may want to check to make sure we are counting all 100 cells and should put a quick sum check in place. Enter “=SUM(F7:O7)” in Q7. Figure 2.2 shows the area that should be created.

5. Next we should create a chart to see what the histogram distribution looks like. Create a column chart using F7:O7 as the only y series and the bucket range as the x series. Once this is created, you may notice a particular distribution. However, press F9 and it should change. Keep pressing F9, and you should see no discernible pattern for the distribution, since the pseudorandom numbers are being drawn from a uniform distribution. We are seeing some variance from a complete set of identical column sizes because we are only using 100 pseudorandom variables. If we extend the random number generation to include more and more numbers we would see the columns start to equalize in size and be more consistent between calculation (i.e., pseudorandom number regeneration). The sheet should look like Figure 2.3. You may also want to name the sheet “Uniform Pseudorandoms from Sht” to distinguish between the upcoming sections.

FIGURE 2.3 Charting the summarized histogram data makes it easy to visualize the results of a simulation.

MODEL BUILDER 2.2: How to Implement Uniform Pseudorandom Number Generation in VBA

Since it is impractical to implement simulation directly on the Excel sheet and since many examples in this book will use VBA, we should also cover how pseudorandom numbers are generated in VBA. Beyond a difference in the function spelling, there are additional steps and nuances regarding the seed state we should be aware of in VBA. To understand these differences, Model Builder 2.2 will recreate the first two steps of Model Builder 2.1 in VBA. The histogram and chart creation are best done on the Excel sheet.

1. In the same workbook as Model Builder 2.1, open the Visual Basic Editor and insert a new module, also insert a new sheet named “Uniform Randoms from VBA”.
2. Start the module with some basic variable declaration and array dimensioning code as follows:

   Sub UniformRandGen()
    Dim rCt As Integer, rMax As Integer
     rMax = 100
    ReDim uArray(1 To rMax, 0)

3. The next part of code is one of the important sections that implements the pseudorandom generator:

   Randomize

   The Randomize statement initializes the pseudorandom number generator in VBA. If it is used on it is own as in the example code then the system timer is set as the seed state.

4. Next enter the following code:

   For rCt = 1 To rMax
       uArray(rCt, 0) = Rnd()
   Next rCt

   This code starts a For Loop that will run for 100 loops (given the value set for rMax). Within the For Loop the array, uArray, is filled with a value provided by the Rnd function. The Rnd function creates a pseudorandom number from a uniform distribution. If it is entered with no parameters after it, as in the example code, Rnd generates the next pseudorandom number in the sequence. While there are a few combinations of using Randomize and Rnd with and without parameters, the way it is set up currently is probably the most useful.

   The second most useful option would be to have the same set of pseudorandom numbers repeat. This can be done using a negative number parameter in the Rnd function. Replace the following line of code:

   uArray(rCt, 0) = Rnd()

   with:

   uArray(rCt, 0) = Rnd(-rCt)

5. The final piece of code exports the array we filled to the Excel worksheet so we can quickly see the results. Prior to entering this final piece of code, go back to the worksheet that you created in this Model Builder and name the range C5:C104, “rng_VBAuRnd”. Now you can go back to the VBE and enter the following code in the same subroutine where you left off:

   Range(“rng_VBAuRnd”) = uArray
   End Sub