Home

Excel ChessGames Viewer

%3CLINGO-SUB%20id%3D%22lingo-sub-765672%22%20slang%3D%22en-US%22%3EExcel%20ChessGames%20Viewer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765672%22%20slang%3D%22en-US%22%3E%3CP%3ETwo%20Excel%20mavens%20incited%20this%20tenacious%20pursuit.%20Thus%2C%20it%20behooves%20me%20to%20gratefully%20recognize%20them%2C%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20%3CSTRONG%3EDiarmuid%20Early%3C%2FSTRONG%3E.%20In%20his%20blog%20post%2C%20he%20wrote%20that%3A%3C%2FP%3E%3CP%3E%E2%80%9C%3CEM%3EIf%20the%20structure%20held%20the%20co-ordinates%20fixed%20and%20figured%20out%20what%20piece%20was%20in%20each%20square%20on%20any%20given%20turn%20(rather%20than%20holding%20the%20pieces%20fixed%20and%20figuring%20out%20their%20co-ordinates%20on%20each%20turn)%20then%20it%20would%20be%20possible%20to%20handle%20%3CSTRONG%3Epawn%20promotion%3C%2FSTRONG%3Ein%20a%20way%20that%20my%20setup%20can%E2%80%99t.%20The%20trade-off%20is%20that%20the%20formula%20for%20figuring%20out%20if%20a%20given%20piece%20could%20have%20moved%20to%20a%20given%20space%20becomes%20way%20%3CSTRONG%3Emore%20complicated%3C%2FSTRONG%3Eif%20it%20needs%20to%20cover%20a%20case%20for%20every%20different%20piece%2C%20compared%20to%20the%20structure%20used%20where%20any%20given%20row%20will%20only%20ever%20contain%20one%20piece.%3C%2FEM%3E%E2%80%9D%20(%3CSTRONG%3EEmphasis%20mine.%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHe%20was%20right.%20To%20handle%20pawn%20promotion%20to%20Queen%20and%20to%20identify%20the%20position%20of%20each%20piece%20after%20every%20move%2C%20I%20inserted%20the%20%3CSTRONG%3ERook%3C%2FSTRONG%3Eand%20%3CSTRONG%3EMoves%3C%2FSTRONG%3Esheets%20and%20defined%20various%20named%20ranges%20and%20complicated%20formulas.%20Please%20view%20Diarmuid%E2%80%99s%20blog%20post%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftheexcelements.com%2F2017%2F08%2F10%2Fdata-visualization-challenge-chess-notation%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftheexcelements.com%2F2017%2F08%2F10%2Fdata-visualization-challenge-chess-notation%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20%3CSTRONG%3EBill%20Szysz%3C%2FSTRONG%3E.%20His%20formula%20was%20regarded%20as%20the%20shortest%20possible%20solution%20to%20the%20%3CSTRONG%3EMaterial%20Gains%20Challenge%3C%2FSTRONG%3E.%20I%20adapted%20such%20formula%20by%20defining%20%3CSTRONG%3EPtLd%3C%2FSTRONG%3Eas%20Pieces!%24E%243%20containing%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUM(COUNTIF(C5%3AJ12%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7B%22B%22%2C%22W%22%7D%26amp%3B%7B%22P%22%3B%22N%22%3B%22B%22%3B%22R%22%3B%22Q%22%7D)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7B-1%2C1%7D*%7B1%3B3%3B3%3B5%3B9%7D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20view%20the%20explanation%20of%20%3CSTRONG%3EXOR%20LX%3C%2FSTRONG%3Eon%20the%20foregoing%20formula%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexcelxor.com%2F2014%2F10%2F22%2Fshortest-formula-challenge-1-results-and-discussion%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelxor.com%2F2014%2F10%2F22%2Fshortest-formula-challenge-1-results-and-discussion%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20noteworthy%20that%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F20200%22%20target%3D%22_blank%22%3E%40Kevin%20Lehrbass%3C%2FA%3E%26nbsp%3Bexpressed%20his%20love%20for%20Excel%20and%20Chess%20in%20both%20of%20the%20foregoing%20links.%20In%20his%20amazement%2C%20he%20created%20a%20blog%20post%20on%20Bill%E2%80%99s%20formula%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.myspreadsheetlab.com%2Fvideo-00162-excel-formula-calculates-value-of-chess-pieces%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myspreadsheetlab.com%2Fvideo-00162-excel-formula-calculates-value-of-chess-pieces%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20amazement%20too%2C%20I%20created%20the%20attached%20Excel%20%3CSTRONG%3EChessGames%3C%2FSTRONG%3EViewer%2C%20which%20includes%20%3CSTRONG%3E73%20of%20the%20Most%20Famous%20Games%20of%20All%20Time%3C%2FSTRONG%3E.%20Although%20you%20may%20not%20love%20Chess%2C%20as%20Kevin%20and%20I%20do%2C%20your%20scrutiny%20of%20the%20formulas%20therein%20shall%20surely%20enhance%20your%20inherent%20analytical%20ability%2C%20without%20which%20you%20shouldn%E2%80%99t%20have%20read%20this%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHappy%20International%20Chess%20Day!%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-765672%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789631%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20ChessGames%20Viewer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789631%22%20slang%3D%22en-US%22%3E%3CP%3EWithout%20the%20crucial%20discovery%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F20200%22%20target%3D%22_blank%22%3E%40Kevin%20Lehrbass%3C%2FA%3E%2C%20I%20wouldn't%20have%20known%2C%20and%20accordingly%20fixed%2C%20the%20bugs%20on%20the%20Pawn%20and%20Rook%20formulas.%20Kevin%20audited%20the%20formulas%20in%20his%20blog%20post%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.myspreadsheetlab.com%2Fexcel-chessgames-viewer%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myspreadsheetlab.com%2Fexcel-chessgames-viewer%2F%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20so%20much%20Kevin!%20For%20those%20who%20share%20our%20love%20for%20Excel%20and%20Chess%2C%20please%20inform%20me%20of%20any%20bug%20you%20may%20discover%20in%20the%20attached%20version%20of%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Twifoo
Trusted Contributor

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!

1 Reply

Without the crucial discovery of @Kevin Lehrbass, I wouldn't have known, and accordingly fixed, the bugs on the Pawn and Rook formulas. Kevin audited the formulas in his blog post here:

https://www.myspreadsheetlab.com/excel-chessgames-viewer/

Thanks so much Kevin! For those who share our love for Excel and Chess, please inform me of any bug you may discover in the attached version of the file.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies