Math on Spreadsheets
Table of Contents
Kauffman-Sheets: (from At Home in the Universe)
Kauffman 10 x 10 Boolean Network
Kauffman N=6 K=3 Fitness Landscape
Eigen-Sheets: (from Laws of the Game: How the Principles of Nature Govern Chance)
Bead Game: Equilibrium (All Black Start)
Bead Game: Equilibrium (Random Start)
Bead Game: Once for all
Bead Game: Selection
Ehrenfest Model with Cooperative Effects
(Right-click on titles to download files)
All the spreadsheets below use an internal iteration counter (e.g., to distinguish between the case of initializing a variable or using a value calculated on a previous iteration). The following spreadsheet just introduces iteration counters.
Iteration Counters. The key trick in any spreadsheet with circular references and iterations is the iteration counter. An iteration counter is given alone in this small Excel file. The spreadsheet is set to run a fixed number of iterations and that number must be entered in the MaxIter box of the spreadsheet. By thus synchronizing the counter with the spreadsheet, the counter will automatically reset itself to 1 at the end of each complete run. When the user aborts a run at a smaller number of iterations (e.g., by clicking the mouse or using the keyboard), then one must reset the iteration counter. This is done by double-clicking on the Index cell (the cell below the label) and hitting Enter, one resets the Index value to 1. This is used in all the spreadsheets below. (Right-click on title to download file.)
The Game of Life. Conway's Game of Life is programmed on this spreadsheet. The spreadsheet runs through "MaxIter" iterations or generations of life (initially set at 100). when Itercnt = 1, each cell is randomly assigned the state of being "alive" (red box) or not. Each (non-boundary) cell has eight neighbors. If exactly three of the neighbors are alive, then the cell is alive in the next generation (regardless of its previous state). If exactly two neighbors are alive, then the cell remains in its present state in the next generation. Otherwise the cell is not alive in the next generation. Cells outside the boundary are fixed as being not alive. To run the program, hit the function key F9 (Calculate Now). Do not touch the keyboard or click mouse until the iterations stop even if the life forms stabilize or vanish (otherwise see instructions above under "Iteration Counters"). The iteration counter internal to Excel runs in the lower left corner. (Right-click on title to download file.)
Glidergun
Epidemic. The model consists of cells each of which can be "sick" or "well" (1 or 0). A cell can be infected by one of its four neighbors (east, west, north, and south) with a probability P. Thus if a cell has two neighbors that are sick, then its chances of being infected on the next round or iteration are PvP = 2P?*P. A cell is infected for only one period or iteration, and then has to be reinfected to be sick again. Thus the contagion or epidemic being modeled is of a disease that only lasts one period and can be passed along or passed back and forth between cells. A cell cannot infect itself so the only contagion can come from its neighbors. The boundary conditions are that the cells outside the boundary are not infected. In the beginning (i.e., Iterations = 1), each cell is infected or not with equal probability. Under these assumptions, the critical value seems to be around 27-28%. Above that a certain portion of the population keeps reinfecting each other. Below that, the infection dies out in enough iterations. (Right-click on title to download file.)
Polya's City. One can model increasing returns-type processes using a Polya's Urn model programmed on a spreadsheet. In the original model, there were an equal number of black and red balls in an urn. If you first draw a red ball, then replace it and replace a black ball by a red one. Remix the balls and then it is a little more likely to draw a red ball the next time. Eventually the red or black balls will dominate and drive out the others. In this spreadsheet version, there are 20 locations (instead of 2 colors) each of which has an initial endowment value 25 (cells A17 to A36). You can change those initial endowments in row 6. The probability of the value in a cell increasing by 1 (or whatever is in the Jump cell) on each iteration is proportional to the average of the value in the cell and the two cells on either side (to add a little "neighborhood" effect) over the sum of all cell values. When 1 is added to a cell, 1/19 is subtracted from the other 19 cells. The two end cells containing the value A17 and A36 are mathematically connected together so that they are neighbors. Thus Polya's City is a circle or ring of neighborhoods. When a location hits zero, it goes "broke." Due to the neighborhood effect, a location that has gone broke can again go positive if it has a non-zero neighbor, and then it is possible for the next broke neighbor to go positive, and so forth?ut that is very improbable. In other words, once a city or cluster has locked in, it will "almost certainly" be sustained with only a few blips around the edges. Forget trickle down. Everywhere else is a wasteland of "broke" neighborhoods as you see above. But it is quite random which block of locations will lock in as successful neighborhoods. Occasionally two groups ("Athens" and "Sparta") will survive after many iterations with wastelands in between. To change the outcome, you need to change the dynamics. (Right-click on title to download file.)
Simple Genetic Algorithm. This is the simplest type of genetic algorithm after the fashion of John Holland (see his book Hidden Order). A model string of 10 0s and 1s is randomly chosen and is fixed throughout the run. There is an initially random population of a fixed number of members (6 in this case) that evolve using selection, mutation, and crossover. Fitness is defined simply as the number of 0s or 1s that match the model string. In each iteration, two members of the population are probabilistically chosen (with the fitness being the weights) to "reproduce" by crossover. Mutation is introduced only for the two members reproducing by crossover. There are two offspring produced by combining the string to the left of randomly chosen crossover point on one parent with the string to right of the crossover point on the other parent. While this reproduction uses the crossover mechanism (with possible mutations in the copying), there is no notion of sex. Indeed the "two" parents could be the same string (cloning). After the two offspring are produced, two members of the old population are probabilistically chosen to be replaced with the offspring where the probability weights are the inverse of fitness (10 minus the fitness). Two models on the spreadsheet start with the same initial population and use the same model string. Thus one can directly compare different mutation rates. With no mutations, the model freezes up rather quickly in a population of clones that just reproduce themselves. With a mutation rate of say 0.015 (the probability that each of the ten elements in a string will "flip" during reproduction), the whole population converges to the model in a hundred or so iterations (with minor mutations away from the "perfect 10"). But with higher mutation probabilities, one sees too much disorder so lower scores are obtained. There is a sweet spot between the crystalline order quickly reached with no mutation and the chaos of too much mutation. Right-click on the name (and choose "Save Target As?quot;) to download the files.