Teaching of Genetic Algorithm with Excel

In a previous post I discussed using Scratch and Excel to model neurones. This post looks at using Excel and six-sided dice as a way of developing insights into how  Genetic Algorithm work, before going on to program one. 

A very simplified version of Tournament Selection is used for the parent selection and the mutation works by rolling a die to get a number between 1-6.

The problem to be solved is to find the lowest values for x and y in the equation 
(x-6)*(x-6)+(y-1)*(y-1).

Routine

1. Using an Excel spreadsheet,  roll two dice six times. Fill in the first two columns with these numbers - these are X and Y values for each solution.
2. The fitness scores should be calculated based on the equation. Low values for this problem are best.
3. 1^st Parent: Roll two dice, if the numbers are same reroll one die to until the numbers are different. Use the two values to select the 1^st parent, the solution with the lowest fitness of the two. Take the X part of the selected parent and it forms the X part of the new child solutions.
4. 2^nd Parent: Roll two dice, if numbers are the same or appear in 1^st parent, reroll until you get two different numbers (including different to the 1^st parent). the solution with the lowest fitness of the two. Take the Y part of the selected parent and it forms the Y part of the new child solution.
5. Mutation: Roll a die for each part of the child solutions. If the roll is 1, roll another die and replace the appropriate element with the new number – even if the same as the previous value.
6. Copy the average  into the table and the lowest value 

7. Copy the child solutions after mutation (orange) into the yellow area and repeat steps 1-6 10 times 

All views and opinions are the author's and do not necessarily reflected those of any organisation they are associated with. Twitter: @scottturneruon