Section 7: A normal distribution simulation (Home Prices program)
Here we give a program that is a bit more complex mathematically. It creates fake data sets of normally-distributed home prices. The user can select how many home prices to generate, the mean price, and the standard deviation of the home prices. The home prices are rounded nicely, to the nearest $500. The program then puts these into the spreadsheet in a rectangular block of cells. (A data set with one or two hundred data points in it should work fine.)
This program was written to generate nice data sets to demonstrate what variance and mean are. (The data sets it creates make pretty histograms.) By the way, Excel will save a spreadsheet in HTML format; if you use this program to generate a batch of home prices, try copying the batch to another sheet of the spreadsheet then save that sheet in HTML form.
The algorithm used in this program is known as the polar method for generating normal variables, and was taken from Simulation, Second Edition by Sheldon M. Ross (San Diego, CA: Academic Press, 1997), section 5.3 (page 72). It should be mentioned that this reference is very clear and very useful; it details how to simulate a variety of random distributions and processes (e.g., queues). The program flow is a little bit tricky: the house prices are put into columns of 20 cells; the right-most column will have fewer than 20 prices in it if the user asks for a number of houses that is not a multiple of 20.
Here is the source code. Here is the spreadsheet program itself. And here is a screen shot: