- BY Kevin Lehrbass
- POSTED IN Reviews
- WITH 2 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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!**

Hello Kevin,

Thanks so much for your compliment. Indeed, there were bugs in the formulas for Pawn and Rook. The original file is intended as the Beta version. I already fixed them in the attached Version 1.0 of the file, which I sent to your email.

For the Pawn formula, the bug was that the formula construed the move on Sequence 26 as if it were a pawn capture en passant. I modified the part of the formula for such captures.

For the Rook formula, the bug was that there was a superfluous comma in the formula. I removed such comma accordingly.

if you find another bug, please inform me so I can fix it. Without your love for Excel and Chess, I wouldn’t have discovered, and accordingly fixed, those bugs!

Cheers,

Robert

Hi Robert,

It’s been so much fun reviewing chess games and auditing your formulas!

Thanks for sharing this with me! I can’t wait to see what you’ll create next.

As for me, I’m working on a mini chess/excel project…I will share it soon hopefully!

Cheers,

Kevin