Two Excel mavens incited this tenacious pursuit. Thus, it behooves me to gratefully recognize them, as follows:
1. Diarmuid Early. In his blog post, he wrote that:
“If the structure held the co-ordinates fixed and figured out what piece was in each square on any given turn (rather than holding the pieces fixed and figuring out their co-ordinates on each turn) then it would be possible to handle pawn promotion in a way that my setup can’t. The trade-off is that the formula for figuring out if a given piece could have moved to a given space becomes way more complicated if it needs to cover a case for every different piece, compared to the structure used where any given row will only ever contain one piece.” (Emphasis mine.)
He was right. To handle pawn promotion to Queen and to identify the position of each piece after every move, I inserted the Rook and Moves sheets and defined various named ranges and complicated formulas. Please view Diarmuid’s blog post here:
https://theexcelements.com/2017/08/10/data-visualization-challenge-chess-notation/
2. Bill Szysz. His formula was regarded as the shortest possible solution to the Material Gains Challenge. I adapted such formula by defining PtLd as Pieces!$E$3 containing:
=SUM(COUNTIF(C5:J12,
{"B","W"}&{"P";"N";"B";"R";"Q"})*
{-1,1}*{1;3;3;5;9})
Please view the explanation of XOR LX on the foregoing formula here:
https://excelxor.com/2014/10/22/shortest-formula-challenge-1-results-and-discussion/
It is noteworthy that @Kevin Lehrbass expressed his love for Excel and Chess in both of the foregoing links. In his amazement, he created a blog post on Bill’s formula here:
https://www.myspreadsheetlab.com/video-00162-excel-formula-calculates-value-of-chess-pieces/
In my amazement too, I created the attached Excel ChessGames Viewer, which includes 73 of the Most Famous Games of All Time. Although you may not love Chess, as Kevin and I do, your scrutiny of the formulas therein shall surely enhance your inherent analytical ability, without which you shouldn’t have read this far.
Happy International Chess Day!
Excel Chess Games Viewer 2.0 can now be downloaded through the link I posted here.
