On July 20 I woke up early and started doing tactics on Chess.com I then noticed an email from Robert Gascon with subject heading “Excel ChessGames Viewer“!  Robert also reminded me that it was International Chess Day!

(download Robert’s amazing Excel ChessGames Viewer)

 

It’s rare that Chess & Excel overlap! What should I do first? Audit formulas? Review games?

 

Sheet ‘Instructions’

See how to add a new game and how to review the moves on the board.

 

Review a Game

Let’s review a classic game! Below we see sheet ‘Board’.

 

 

 

 

 

 

 

 

 

 

Select a game: in cell B4 I selected ‘Kasparov’s Brilliancy’.

See the moves: click the “^” spinner button on the right.

The next move is the brilliant move! Can you see it?

Hold spin buttons to quickly cycle through moves or enter sequence number in the cell K6.

 

How do the pieces move?

Everything starts with the game you select in cell B4. Cells B5 & B6 retrieve game details.

In cell B9 we see this key formula:

=LOOKUP(IFERROR(LOOKUP(2,
1/(CHOOSE(MoveChoice,WhiteMoves,BlackMoves)=B$8&$A9),
CHOOSE(MoveChoice,WhiteLabels,BlackLabels)),”ET”),PieceIcons)

There are two LOOKUP functions in this formula. Let’s examine the inner LOOKUP.

LOOKUP’s lookup_value is hard-coded to 2 (I’ll explain later).

 

1/(CHOOSE(MoveChoice,WhiteMoves,BlackMoves)=B$8&$A9) is the lookup_vector (where we look).

Select lookup_vector and press F9 key to see this:

 

 

Why so many errors? Because of =B$8&$A9  Only the current cell’s a8 co-ordinate matches the result of CHOOSE(MoveChoice,WhiteMoves,BlackMoves). You’ll see a 1 above in the 19th position.

 

CHOOSE(MoveChoice,WhiteLabels,BlackLabels) is the result_vector (answer we retrieve).

Select result_vector and press F9 key to see this:

 

 

The lookup_value is hard-coded to 2. We won’t find 2 in lookup_vector results so LOOKUP defaults to last value of 1 (19th position). Answer is BR (position 19 of results_vector) that’s used in the outer LOOKUP function!

=LOOKUP(“BR”,PieceIcons) look for BR(black rook) in named range PieceIcons (=Board!$L$9:$M$21)

 

Continue Auditing

To get all the way back to the raw data (sheet ‘Games’) you’ll have to audit these named ranges found inside the double LOOKUP function:

• MoveChoice
• WhiteMoves
• BlackMoves
• WhiteLabels
• BlackLabels

 

Here are some auditing tips:

• click inside formula bar to see referenced cells
• unhide columns L & M (to see chess icons)
• note spinner button values hidden underneath it
• use F9 key on each part to see results (then press ‘Esc’)
• audit named ranges carefully

There are 45 named ranges. Select Formulas / Name Manager. Here’s a sample:

 

 

 

 

 

 

 

 

 

 

 

Sheet ‘Games’

Starting in column B each column is a game with moves starting in row 8. This is modern chess notation. Each row contains a move from white and black.

Column F’s game is called ‘Amazing Nakamura’. I played through the moves and IT IS amazing!

 

Material Advantage

Cell B17 (sheet Board) displays chess piece material advantage. Note: a material advantage doesn’t necessarily mean a player is winning.

=CHOOSE((PtLd>-1)+(PtLd>0)+1,”Black”,”None”,”White”)&” has a”&
IF(PtLd,” “&ABS(PtLd)&”-“,”ny “)&
“point lead in chesspieces.”

Formula above uses named range PtLd (sheet Pieces) where the calculation happens. PtLd formula is:

=SUM(COUNTIF(C5:J12,
{“B”,”W”}&{“P”;”N”;”B”;”R”;”Q”})*
{-1,1}*{1;3;3;5;9})

This calculates the value of the pieces. See this post for a detailed explanation.

 

The Author

Robert H. Gascon is Certified Public Accountant from Quezon City, Philippines.

See his Microsoft tech community profile. Robert is a valued contributor meaning that he answers a lot of questions posted by Excel users.

Robert has an incredibly deep knowledge of Microsoft Excel.

Robert has shared alternative solutions on my blog. I’ve learned a lot from him. Thank you Robert!

• how-many-unique-list-1-names-found-in-list-2
• sorted-data-validation-list
• largest-number-inside-alphanumeric-string
• dynamic-ranges-using-index-function
• what-is-this-formula-doing
• concatenate-values-to-create-a-key-in-excel
• allocating-costs-in-microsoft-excel

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham (Canada).

In 2018 I visited New York City. In central park I found outdoor chess tables and an indoor chess club.

I’ve played chess since I was 11. I was on the chess team in high school. Chess is fascinating and it’s great mental exercise. In the 90s I discovered Excel. I’ve been hooked ever since that day!

And…July 20th was International Chess Day!