Section 6: A Coin Toss Simulation
Here we give a more substantial example of a spreadsheet program. This program simulates coin tosses, demonstrating the Bernoulli distribution. Below, we give a screen shot and source code for the program; the spreadsheet program itself is available here.
Here is the source code. This program allows the user to select how many times to toss 10 coins; to run the program, the user clicks on a button in the spreadsheet (not visible in the screen shot below). In the program, the simulation proceeds in a very direct sort of way: A for-next loop controlls the number of times the coins are tossed; in each turn of the loop, another for-next loop and a random function are used to simulate the toss of the coins. Interesting things to note in the code include how the random function is used (the "raw" random function returns a decimal value between 0 and 1). Also, note how an array is declared and used. In the source code, a subroutine for the command button is given; the button itself was placed in the spreadsheet as described in section 5 of these notes (and the subroutine shell code automatically generated as described there). One more thing to note: the program includes a bar graph showing the outcome of the coin tosses. This requires no programming -- when the program is complete and running, just use the Chart Wizard in Excel to produce a bar graph wired to the data in the spreadsheet, as usual (it automatically changes when the program is run and the outcome numbers change).
One deficiency of the code is that no attempt has been made to protect against bad user input; this might be a useful embellishment (see section 4 of these notes). By the way, the algorithm might seem to be a bit simple-minded, but it is surprisingly fast; a user will have no trouble simulating thousands of tosses of the ten coins.
Here is a typical screen shot. User input is being queried, while results from a previous run are visible. Note that some care has been taken to nicely format the spreadsheet "plane"; this was done by hand after the programming was done.