J-Walk & Associates, Inc.

Chess Game Viewer

This is one of the most creative Excel apps I've ever seen. Created by Daniel Ferry, At Excel Hero: Excel 2007 Chess Game Viewer.

It uses no macros. Unprotect the worksheet and try to figure out how it work. Some hints:

• Dynamic Charting with No VBA
• Combination of XY (Scatter) and Stacked Column chart types
• Marker Fill with picture files.
• Conditional Chart Formatting for chessboard style
• No IF() functions. Not even one. REPT() and boolean logic as alternative to IF() INDEX/MATCH

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Oscar. Comment posted 14 February, 2010 12:34pm

   Interesting! What are the advantages of using boolean logic and REPT() instead of IF()?
2. By Daniel Ferry. Comment posted 14 February, 2010 4:28pm

   @John-
   Thank you very much for the kind words, and for showcasing the project on your blog!
   
   @Oscar-
   I try to avoid IF() functions as much as possible. They can lead to convoluted formulas. I prefer to reserve them for when there is no other alternative. Excel versions prior to 2007 only allowed the nesting of IF() functions to seven levels deep. While this is no longer a problem, I still feel it is better to reserve their use.
   
   The REPT is only required if the output of a formula is text. The boolean logic is used in both text and numeric output. These approaches enable the calculation engine to march straight through the formula with no branching. They are easier to read; often shorter, especially the numeric output variety. I believe (but have not tested) that they are quicker as well.
   
   I've have a post on my blog that goes into much more detail:
   
   http://www.excelhero.com/blog/2010/01/i-heart-if.html
   
   Regards,
   
   Daniel Ferry
   excelhero.com/blog
3. By Oscar. Comment posted 15 February, 2010 3:55am

   David Ferry,
   
   Thanks for your answer! Your blog is outstanding! Very interesting.
   
   How do you convert Countif(A1:A2,C3:F6) + CSE to boolean logic?
   
   /Oscar
4. By Henry Rios. Comment posted 15 February, 2010 6:36am

   Wow!! espectacular! :O
5. By Daniel Ferry. Comment posted 15 February, 2010 10:15am

   Oscar-
   
   You would use boolean logic with SUMPRODUCT.
   
   I have a post on my blog that details how:
   
   http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html
   
   Regards,
   
   Daniel Ferry
   excelhero.com/blog
6. By L. Quezada. Comment posted 15 February, 2010 4:05pm

   It works on Excel 2003?
7. By John Walkenbach. Comment posted 15 February, 2010 5:21pm

   It's an Excel 2007 files, but it might work if you've installed Microsoft's Office Compatibility Pack (which adds support for the new xlsx file format).