Kill Time At Work With Recreational Math: Conway's Game of Life

This is the sixth in my series of killing time at work using math (here are the others 1, 2, 3, 4 and 5).

If you have read my posts before you already know that there are a few prerequisites for this:

• You like recreational math.
• You work at a computer and have access to Microsoft Excel.
• You are bored out of your tree.
• You don't want to get caught slacking off.

Let's get started.

---

Conway's game of life is an example of a simple cellular automaton created by John Conway in 1970.

The rules are, as I said, quite simple. Set up a rectangular grid, these will be the 'cells'.

Set some of those cells to 'alive' (=".") and some of the cells to 'dead' (=""). This will be the first generation.

The next generation will depend on the state of the grid in the first generation. To do this look at each cell one by one and apply the following rules:

• A living cell that has fewer than 2 living neighbours will die (as if caused by under-population).
• A living cell with 2 or 3 living neighbours will live on to the next generation (this would simulate an optimal population density).
• A living cell with more than 3 living neighbours will die (as if caused by overpopulation and famine).
• If a cell is dead (empty) and has exactly 3 living neighbours it will become a living cell (as if by reproduction).
• Repeat the whole process all over again to create the 3^rd generation, etc, etc.

From these simple rules amazingly complex patterns emerge.

Excel Calculation Options

1. Go to the File tab in Excel and choose Options.
2. When the Options dialogue pops up choose Formulas.
3. The Dialogue for formula/calcuation options will appear.
4. In this dialogue make sure you set the maximum number of iterations for calculations to 1 (circled in red). If you don't Excel will simply scream through all of the calculations and you will not see anything except for the final generation.

The Spreadsheet

We want to get this task done without having to resort to VBA programming in Excel. That means doing it solely using the spreadsheet functions. Since Excel doesn't have loops we will therefore need to use multiple grids as shown below.

These are the Initializer Grid, Grid 1 and Grid 2. Grid 2 has to be below Grid 1 because of the order in which Excel checks and updates cells (it scans from left to right and top to bottom).

Set up a spreadsheet with 3 grid that are 12 x 12. The borders of each grid will be set to a colour of your choice and will remain empty.

---

Cell C2: This is the Reset Cell. Set this cell to equal 1 to get everything initialized. When set to 1 it tells grid 1 to clear everything and set the cells to whatever is in the Initializer Grid.

Once that is done then delete the value in the cell or set it to 0. Grids 1 and 2 will automatically update once this is done (you will have to hit F9 to get each iteration to update).

---

Cell O2: This toggles between the values 1 and 2. It tells Grid 1 and Grid 2 which one will be the active grid.

Cell equation:
=IF(C2=1,1,MOD(O2,2)+1)

---

Initialization Grid

Cells C6 to L15: This is the initialization grid.

• The living cells are set to the period character: .
• The dead cells are set to double quotes: ""
• Set up the cells in any way you like. For my example above I created a simple glider pattern.

---

Grid 1

Cells P6 to Y15: This is Grid 1.

The equation is complex. I will show it first and then explain it near the bottom of this post.

Cell P6 equation:

=IF($C$2=1,C6,
IF($O$2=2,
P6,
IF(P20="",
IF(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")=3,".",""),
CHOOSE(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")+1,"","",".",".","","","","","",))))

Just copy and paste the above equation into the other 99 cells. Excel will auto-update the relative references for you.

---

Grid 2

Cells P20 to Y29: This is Grid 2.

This equation is also complex. I will show it first and then explain it near the bottom of this post.

Cell P20 equation:

=IF($C$2=1,"",
IF($O$2=1,P20,
IF(P6="",
IF(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")=3,".",""),
CHOOSE(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")+1,"","",".",".","","","","","",))))

Just copy and paste the above equation into the other 99 cells. Excel will auto-update the relative references for you.

The Results

First, I set the reset cell C2 to 1. Then I hit F9 to get the grids to reset.
Then I clear the reset cell and hit F9 again, and again, and again. The glider pattern moved along in the grid just as expected. Nice.

Here are the first 5 iterations. It keeps going until it hits the grid wall and then dies off.

Iteration 1


Iteration 2


Iteration 3


Iteration 4


Iteration 5

The Gory Details

If you want to simply copy the above equations into your spreadsheet then feel free to skip this section. Otherwise read on.

Remember Grid 2 mirrors Grid 1 in one iteration, then the toggle cell $O$2 toggles and the roles reverse (Grid 1 mirrors Grid 2). This is how you get around not having a loop function in Excel.

Grid 1 Equation Explained

Once again here is Cell P6 equation from Grid 1:
=IF($C$2=1,C6,
IF($O$2=2,
P6,
IF(P20="",
IF(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")=3,".",""),
CHOOSE(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")+1,"","",".",".","","","","","",))))

---

=IF($C$2=1,C6,
This part simply checks if the rest switch is set to 1. If true then the cell is set to the corresponding value in the initializer grid.

---

IF($O$2=2,
P6,
This is checks the toggle in $O$2. If it is 2 then the cell remains unchanged and is set to it current value (P6).

---

IF(P20="",
IF(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")=3,".",""),
This part checks if the cell in the Grid 2 (the mirror grid) is 'dead'. If it is dead then it uses the COUNTIF function to add up all of the living cells in mirror locations in Grid 2 (those with period symbols in them). If the count adds up to 3 the cell becomes alive.

---

CHOOSE(COUNTIF(O19:Q19,".")+COUNTIF(Q20,".")+COUNTIF(O21:Q21,".")+COUNTIF(O20,".")+1,"","",".",".","","","","","",))))
This part activates if the cell is 'alive'. It also uses the same COUNTIF equation to count up the number of living cells in the mirror locations in Grid 2. It then uses the CHOOSE function to choose a final value among all the several Conway rules.

---

Grid 2 Equation Explained

Once again here is Cell P20 equation from Grid 2. Remember Grid 2 mirrors Grid 1 and vice-versa as the toggle cell ($O$2) toggles between 1 and 2:

=IF($C$2=1,"",
IF($O$2=1,P20,
IF(P6="",
IF(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")=3,".",""),
CHOOSE(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")+1,"","",".",".","","","","","",))))

---

=IF($C$2=1,"",
This part clears the cell in Grid 2 if the reset switch is set to 1.

---

IF($O$2=1,P20,
This is checks the toggle in $O$2. If it is 1 then the cell remains unchanged and is set to it current value (P20).

---

IF(P6="",
IF(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")=3,".",""),

This part checks if the cell in the Grid 1 (the mirror grid) is 'dead'. If it is dead then it uses the COUNTIF function to add up all of the living cells in mirror locations in Grid 1 (those with period symbols in them). If the count adds up to 3 the cell becomes alive.

---

CHOOSE(COUNTIF(O5:Q5,".")+COUNTIF(Q6,".")+COUNTIF(O7:Q7,".")+COUNTIF(O6,".")+1,"","",".",".","","","","","",))))
This part activates if the cell is 'alive'. It also uses the same COUNTIF equation to count up the number of living cells in the mirror locations in mirror Grid 1. It then uses the CHOOSE function to choose a final value among all the several Conway rules.

Conditional Formatting

To make the grid pretty and the cells light up with nice colours you can set up some conditional formatting.

1. Click on Cell P6 and choose Conditional Formatting in the Home Tab.
2. Choose New Rule.
3. Choose "Use a formula to determine which cells to format"
4. In the formula box type in =P6="." as shown below. Then click the format button and set the format to the colour of your preference.
5. Copy that cell to the other 99 in the grid.
6. Do a similar procedure for Grid 1 and Grid 2.

Closing Words

This was quite a tricky spreadsheet to get right.

It took me a few hours to adapt the instructions from the reference cited below into my own spreadsheet but it was well worth it as I learned a few new useful Excel tricks.

If you set up this spreadsheet yourself, try other patterns and also try to make the grid bigger than 10 x 10.

Thank you for reading my post.

Image Sources

All images in this post are produced by the author.

Post Sources

This post was inspired by and adapted from this link: http://www.tushar-mehta.com/publish_train/xl_vba_cases/0906%20conways%20game%20of%20life.shtml

Conway's Game of Life (Wikipedia): https://en.wikipedia.org/wiki/Conway's_Game_of_Life