Traducir el blog

Data Conversion 3: Chess Game with formulas

Posted on agosto 29, 2012 by Excel Pedro Wave

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:

  1. Chess game between two humans programmed in Excel only with iterative formulas, without macros or VBA.
  2. 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.
  3. Touch of the pieces and squares with 64 radio buttons linked to a single cell: Square.
  4. Rules of short and long castling.
  5. En passant pawn capture.
  6. Option to view the game from the side of the white or the black.
  7. Option to view the cell references in algebraic or numeric notation.
  8. Option to select the style of chess pieces (figurines) or numeric or algebraic style.
  9. Option to display the last movement of a piece, its possible legal moves and attack positions of the white or black pieces.
  10. Option to select the chess game number.
  11. Option to start a new game.
  12. Panel to display each of the moves of the game in numeric notation. Algebraic notation will be added later.
Yet to be resolved:
  1. Valid positions in the king check.
  2. Checkmate detection.
  3. Promoting pawns.
  4. Save chess games in algebraic notation.
  5. Save FEN notation.
  6. 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.

Etiquetas: , , ,

2 Response to "Data Conversion 3: Chess Game with formulas"

.
gravatar
VerZul Says....
11 noviembre, 2012 10:02

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.

.
gravatar
Unknown Says....
05 noviembre, 2013 12:49

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.

Suscribirse a: Enviar comentarios (Atom)

Mi lista de blogs

Mostrar todo