### Teaching of Genetic Algorithm with Excel

Originally posted in https://computingnorthampton.blogspot.co.uk/2017/01/genetic-algorithm-teaching-with-excel.html

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

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

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

- 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.
- The fitness scores should be calculated based on the equation. Low values for this problem are best.
- 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. - 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. - 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.
- Copy the average into the table and the lowest value

- 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

## Comments

## Post a Comment