Article 13: Solving the game Mastermind Using Excel, and Visual Logic.

Introduction

Mastermind is a game in which a player solves a hidden code using a sequence of trials.

Of course, one can choose whether to buy the game or just use a pen and paper.

The game is generally suitable for 12+, or particularly bright under 12 children.

You can play it by yourself using Excel (using a workbook available from the author on request).

Mastermind is a pure logic game – and should be taught to all kids in Primary School.

This is because Mastermind teaches logic. However, one can understand the game at various levels of depth.

1. For a child it can be used to teach basic logic.
2. For a University student it can be taught to teach probability theory
3. For a physicist it can be used to teach the probabilistic basis for thermodynamics.
4. For an advanced maths student it can be used to teach the foundations of cryptology.
5. For a machine learning student it can be used to teach the foundations of Machine Learning.

It is no wonder then that there are a number of published scientific articles that discuss Mastermind.

This article focuses on how to create the Mastermind game in Excel and how to solve Mastermind using Visual Logic.

Excel Games

There already exists several Excel-based Mastermind games.

This includes:

Excel Mastermind (excelgame.net)

The corresponding host site Excel Games is particularly interesting as it contains numerous classic games in Excel.

?Another site of interest is: Mastermind | Excel using only formulas (youtube.com)? This video is within the channel petes.spreadsheets, and there are a number of variations; and other games.

Creating Mastermind game

As stated, the purpose of creating the Mastermind games (by the author) is for longer-term objectives, and I was unsure if using existing available games could be advantageous or cumbersome. So I developed one myself.

Noteworthy is that in Pete’s spreadsheets the Excel formulae are particularly long – and it appeared that for my purposes it was going to be a lot easier if I limited the variation of Mastermind to having a digit in one position only.

Similarly, the use of coloured pegs instead of digits was certainly aesthetically superior but also somewhat more difficult; so I limited the game to digits 1 -9.? Indeed, even using the digit 0 was going to make things harder (sorry Indian mathematicians!).

With these adaptations the game was fairly easy to construct.

Using VBA

For whatever reason, many Excel users see VBA as a form of cheating, and with great pride do not use VBA. As I am more pragmatic I use whatever is easiest – and consequently have no hesitation using VBA when required.

By using VBA, creating the hidden code only involved using RandBetween (1,9) to create each digit in each position.

The author (to focus on various levels of solution) created various versions of the game (and solution approach). Here we focus on the Excel version of Mastermind that uses visual logic.

Simple Interface

Figure 1 shows a simple interface to play Mastermind. ‘Start’ creates a hidden code. The player then constructs a trial sequence, and presses ‘Score’. Once you know the sequence press ‘Show Code’ to confirm.

Helper Tables

Helper tables are also available.

These are shown in Figure 2.

Comments are added to identify when you have some insight into the sequence. The Position/Digit table (or visual logical table) indicates which combination of digit/position is feasible.

A YouTube Video (see Reference) is available to explain the process.

Visual Logic

The second table in Figure 2 is a Visual Logic table. It is interesting to note that Visual Logic is generally considered as flowcharts. I would certainly agree that flowcharts are a type of Visual Logic; but would not agree that Visual Logic is restricted to flowcharts. Therefore the use of the table is an easy example of Visual Logic that is straightforward for a child to understand – and may well be new to many adults.

Example solution

Here I show the observed tables for a worked example. This corresponds to the YouTube video example.

Reference

MidasTech 2024, Solving MasterMind using Excel and Visual Logic YouTube video.