Chess game with iterative formulas
In Excel there is something to those who we like the formulas that takes
us out of our squares, when you see a circular reference, because a cell
refers to itself in a direct or indirect way, forcing to remove or correct
the circular reference or, as is the case in this game, to accept it to
work by changing the number of times Excel iterates a formula.
Iteration is the repeated recalculation of a worksheet until a specific
numeric condition is met. Excel cannot automatically calculate a formula
that refers to the cell — either directly or indirectly — that contains
the formula. To use iterative formulas we must change Excel options:
- Enable iterative calculation.
- Maximum number of iterations = 1 in this game.
Microsoft.com: How to change formula recalculation, iteration, or
precision
Rules and norms of the game of chess
The first rule that I have in mind is the classic:
Touch-move rule
Using only formulas, how pieces are touched? How do you get the piece
touched? How to know the destination square?
The trick has been to insert 64 option buttons from form controls, one for each of the 8x8 = 64 squares of the chessboard,
called Square11 to Square88, using the nomenclature of numerical chess
notation. They are the 64 forms that are grouped in the Squares, as seen in
the image to the right of this text to all forms in the Chessgame
sheet.
These radio buttons are not visible through the squares were extended
five times and, with a zoom of 20%, the buttons appear to the hinges of
the doors of each square box. Each of these buttons takes the size of a
square and it is linked to the same cell: Square. Pressing each button
generates a different number from 1 to 64, so you know exactly what box is
down.
The position of the piece played is stored in the cell: PositionPiece,
until you touch a new square that is a valid position of the played piece,
if it is found with cell formulas into MirrorSquare and Move.
Chess Game Download
You can download these chess game following this link:
Chess Game Specifications
This game lets you play chess between two humans. No takes a game
engine (impossible with formulas in Excel, it would not be effective and
bad performance).
Is intended to generate a chessboard with 32 initial pieces to review
the moves allowed by the rules of chess and to store games in an Excel
spreadsheet to do the database of games played in the NumericGames
sheet.
The specifications of the chess game are:
-
Chess game between two humans programmed in Excel only with iterative
formulas, without macros or VBA.
-
Chessboard of 64 squares as a resizable form with the ability to flip
the board on the side of the white or the black pieces.
-
Touch of the pieces and squares with 64 radio buttons linked to a
single cell: Square.
-
Rules of short and long castling.
-
En passant pawn capture.
-
Option to view the game from the side of the white or the
black.
-
Option to view the cell references in algebraic or numeric
notation.
-
Option to select the style of chess pieces (figurines) or numeric or
algebraic style.
-
Option to display the last movement of a piece, its possible legal
moves and attack positions of the white or black pieces.
-
Option to select the chess game number.
-
Option to start a new game.
-
Panel to display each of the moves of the game in numeric notation.
Algebraic notation will be added later.
Yet to be resolved:
-
Valid positions in the king check.
-
Checkmate detection.
-
Promoting pawns.
-
Save chess games in algebraic notation.
-
Save FEN notation.
-
Translate the game into several languages.
I hope you enjoy and make you enjoy such good times as I have been
debugging this chess game.
To further improve this game I wait for your supportive
comments.
2 Response to "Data Conversion 3: Chess Game with formulas"
Hello Peter,
Even if I am not follower of apply all this rules while playing chess, it’s impresive that you have created this entire file only with formulas. Where is your limit?
The idea of using CheckBox attached to cells to keep record of the first hitting piece seems brilliant to me, very ingenious.
I hope I’ll be able to play some day, when the file is completely finished, a game with you m8.
Regards my friend.
Tableau Software Consultancy
Designed properly, a good business dashboard is an indispensable tool that informs with a glance.SQIAR http://www.sqiar.com/solutions/technology/tableau/ is a leading Business Intelligence Consultancy; provides Tableau Software consultancy in United Kingdom and USA
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.