Notes on Lab #7

For the first hour of this lab I will give a lecture on Module 9.4, Random Numbers from Various Distributions. Then we will build as many of the random number generators from Project 1 (p. 387) as we have time for, using Excel.

To get started, open Excel and fill a column with a list of 1000 random numbers, using the RAND function, which takes no inputs and returns random number between 0 and 1. (We're using 1000 as a reasonable approximation to infinity, which is what the Law of Large numbers tells use we really need.) These will be your values for the rand variable from the Box-Muller-Gauss method. Next to this column generate another 1000 random values between 0 and 2Π to use as the values for the a variable. Then compute the values for b from the BMG method. (You can use the standard parameters μ=0, σ=1.) Next use the formula to obtain the normally-distributed values from the sine or cosine function. Note how the random numbers can change when you enter or modify a formula -- Excel recalculates everything "behind the scenes", which for random numbers means regenerating them.

Now you can visually compare your Box-Muller-Gauss values to the expected normal distribution, using a histogram. This page shows you how to do histograms in Excel. You may have to adjust the minimum and maximum bin boundary values to match the range of your generated numbers. You can find this range by using Excel's MIN and MAX functions, or by making a reasonable guess.

Next, try the Exponential Method. You can reuse your rand values from the previous method, and generate a new set of data by plugging these values into the Exponential Method formula from the lecture notes. Generate and plot a histogram with reasonable min and max values.

Finally, use the Rejection Method to generate a bunch of normally-distributed random values, and plot a histogram. For this method you'll have to be a bit clever: Excel's IF function requires you to specify a value for when the condition is not met. By choosing an appropriate value, you can filter or sort the resulting data to keep only the meaningful values. You'll have fewer than the 1000 values you started with, but you should still have enough for a reasonable histogram.

Turn in your Excel spreadsheet and a brief writeup. For your writeup, show histograms of the numbers you generated using the various methods. If you have time, you might try creating your own random numbers by using a linear congruential generator and dividing by the modulus. If you do this, compare the results with the results you get from Excel's RAND function. You might also compare your Box-Muller-Gauss results with the built-in normal random number generator in the Data Analysis add-in.