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__.
This
is the famous "glidergun" configuration in the Game of Life.
The configuration continuously reproduces itself all the while emitting
a "glider" towards the southeast corner of the rectangle.
Hit the F9 key to run it. (Right-click on title to download file.)

__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.