I visit many different Excel blogs. Every once in a while I learn something completely new that really boggles my mind! And in this case the Chess world overlaps the Excel world so it was REALLY amazing (and nerdy)! How do I start to explain how awesome this is?

The Formula!

This is Bill Szysz‘s  winning formula from an Excel Challenge by excelxor.com!

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

Here is Excelxor’s challenge post and solution post !

Formula Challenge

In this Excel screenshot WQ = white queen, BB = black bishop, WP = white pawn etc.

Depending on position, the pawn is the weakest piece (1 point), followed by knight (3 points), bishop (3 points), rook (5 points) and queen (9 points).

Challenge: calculate numerical values of white’s pieces and black’s pieces. Shortest formula wins.

Excel File & YouTube Video

Here is the Excel file that I used for this post. Here is my YouTube video.

Why Is It So Awesome?

We could list all pieces in a column and use a simple countif but that wouldn’t be as much fun! The white and black pieces (i.e. BP, WP, BN, etc) are created dynamically inside the countif formula!

Dynamically? Yes! The formula criteria uses two array constants. Array constant #1 {“B”,”W”} “B” = Black, “W” = White, which is then concatenated with array constant #2 {“P”;”N”;”B”;”R”;”Q”} representing all the chess pieces (except for the King). 

How? Here is the trick! A comma separates the colors while semi colons separate the pieces as they go in different directions (dimensions). When they are concatenated we get all the combinations between the two sets of letters!!!

You start with this {“B”,”W”}&{“P”;”N”;”B”;”R”;”Q”} and end up with this {“BP”,”WP”;”BN”,”WN”;”BB”,”WB”;”BR”,”WR”;”BQ”,”WQ”}

Highlight the ‘criteria’ part of the countif function and press F9 key (or Fn & F9 on a laptop)

But Wait There’s More!

The COUNTIF’s work is finished once the combinations between the two sets off letters are created and counted. The results (plural) of the COUNTIF are then multiplied by *{-1,1}*{1;3;3;5;9}

WHAT?? First we have to distinguish the black pieces from the white pieces hence the {-1,1}. Black pieces multiplied by -1 and white pieces by 1.

Then {1;3;3;5;9} which goes back to the numerical value of the pieces. In the end we have negative numbers for black and positive numbers for white.

In the screen shot above, when we add them all up we -3. This means that black has 3 additional points worth of piece material compared to white (it doesn’t necessarily mean that black is winning). A positive number would mean that white has more material.

For an Excel and Chess nerd like me this solution was I N C R E D I B L E !!!

I re-created the chess board in Excel that calculates the value of the pieces. I also included a drop down list so that you can change the chess that appear on the board. This changes the answer.

The Man Behind The Winning Formula

This is Microsoft Excel legend Bill Szysz. Check out his YouTube channel.

…the story ends here but I continued to play around with this concept…..

Is There A Practical Application For This?

How about creating all combinations from two lists. There are various ways to do this:

• cartesian product in a database
• pivot table trick (my video)
• traditional excel formula (scroll to bottom of post)
• vba code

Let’s have some fun and use the method that we’ve learned from Bill Szysz.

Method A: The Quickie

1. Create separate tables for each text list

2. Select a range of cells consisting of 4 rows and 7 columns

3. Type =B3:B6&TRANSPOSE(D3:D9) and press CONTROL SHIFT ENTER

It’s a quick way to display all of the combinations but it’s not dynamic if we add more text and the text is in a block not in a single column (or row).

Method B: The Dynamic

1. Create separate tables for each text list

2. Create a counter (manual drag or via formula)

3. Use COUNTA functions to count text values in each table

In cell I1 add this formula  =COUNTA(Table2[List 2])

In cell J1 add this formula  =I1*COUNTA(Table1[List 1])

4. Use this formula to repeat based on the number of text values per list

In cell H3 add this formula  =IF($G3>$J$1,””,IF($G3=1,1,IF($I2=$I$1,H2+1,H2)))

In cell I3 add this formula   =IF($G3>$J$1,””,MOD($G3-1,I$1)+1)

5. This is our final formula that displays the text values!

In cell J3 add this formula =IF(I3=””,””,INDEX(Table1[List 1]&TRANSPOSE(Table2[List 2]),H3,I3)) and instead of pressing ‘Enter’ like you normally would press ‘Control Shift Enter’ as this is an array formula.

Let’s Test The Solution

Type in text directly below List 1 and List 2. This will automatically expand the table(s) and you should see the values in ‘COMBINED LIST’ (column J) expand. Note that the formula in cell J1 is the total number of combined text items. You may have to drag down the formulas if you add a lot of text to the lists.

Do you see how I used the IF function in many of the formulas above. I like to give the formula a reason to do nothing. Once we have displayed all of the ‘COMBINED LIST’ items there is no reason for the formulas to calculate anything.

Traditional Excel Formula

This is a more traditional way to concatenate using two OFFSET functions (or INDEX functions).

OFFSET(Table1[[#Headers],[List 1]],H3,0)&OFFSET(Table2[[#Headers],[List 2]],I3,0)

This method also requires the counters. I guess you could argue that it’s better because it’s a bit shorter and it isn’t an array. We just use the counters to loop through each table and get a single value from each table. 

NOTE: Let’s not forget that with Excelxor’s method there was no need to make it expandable. The number of unique chess pieces is a constant and will never change. This is why the text values were hard coded inside of the formula like this:  {“B”,”W”}&{“P”;”N”;”B”;”R”;”Q”} and there was no need to extract them.

Summary

Why learn Excelxor’s method if it might not be the easiest method for creating a Cartesian product? It’s important for me is to continue to expand my knowledge in Excel. The more knowledge that I have enables me to provide better and quicker solutions. What I learned from Excelxor’s blog is amazing because it creates all the combinations inside of the formula. There have been many times when I’ve learned something new and then applied it in a practical way to help someone in Excel!

About Me

Me and my {“B”,”W”} dogs Cali and Fenton!

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

There are so many amazing things that you can do with Excel. Check out my videos and my blog posts.

Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂