Last week I set readers the challenge which can be found here.

Two correct answers received (three if you count **Snakehips**‘ improvement) from **GreasySpot** and **Bill Szysz**, of which Bill’s was the shorter of the two (74 characters compared to 249, excluding the equals sign).

Snakehips then came along and improved this to a mere 70 characters simply by making the reference to the required range relative. (I can imagine Bill is now kicking himself for using absolute references in a shortest-formula challenge!)

Anyway, between the two of them they managed to come up with what is indeed (at least, that I know of) the shortest possible solution to this problem, and that solution is:

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

**How does it work?**

Not surprisingly, this being a shortest formula challenge, the key point here is *abbreviation*. In essence, the above solution uses several techniques in which we can make as short as possible the five principal tasks of:

1) Counting the number of Pawns (P), Knights (N), Bishops (B), Rooks (R) and Queens (Q) within the range

2) Assigning a value to each of these pieces (P=1, N=3, B=3, R=5, Q=9)

3) Determining how many of these are either White – W – or Black – B – pieces

4) Summing the assigned values from 2) for each piece determined from 3) for each of White and Black

5) Subtracting the resulting total for Black from that for White

Firstly, instead of passing a single array of 10 strings to our COUNTIF statement, i.e. “WP”, “BP”, “WN”, “BN”, “WB”, “BB”, “WR”, “BR”, “WQ” and “BQ” (which of course would be perfectly fine), we can abbreviate this by first performing a concatenation of two orthogonal arrays, one being a single-row array, the other a single-column array (it’s not important which is which). Hence, a construction such as:

{"B","W"}&{"P";"N";"B";"R";"Q"}

in which the first array is a 1-row-by-2-column array and the second a 5-row-by-1-column array, will generate a 5-row-by-2-column array, the entries in which will be precisely those ten I outlined above, i.e.:

{"BP","WP";"BN","WN";"BB","WB";"BR","WR";"BQ","WQ"}

Since the above is a two-dimensional array, we might potentially face difficulties in passing it to some functions. However, COUNTIF is perfectly capable of processing arrays of such dimensions for the *criteria* parameter, and so we need not worry here.

Hence we see that:

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

becomes:

=SUM(COUNTIF(A1:H8,{"BP","WP";"BN","WN";"BB","WB";"BR","WR";"BQ","WQ"})*{-1,1}*{1;3;3;5;9})

And resolving the COUNTIF construction gives:

=SUM({5,5;1,1;2,1;1,1;1,1}*{-1,1}*{1;3;3;5;9})

as can easily be verified.

Now, observe that each of the first elements in this set of 5 “pairs” corresponds to a count for one of the Black pieces, and the second element to a count for the equivalent White piece. If I just temporarily colour the counts for White as red in the array obtained from the COUNTIF:

{5,5;1,1;2,1;1,1;1,1}

then hopefully things become a little clearer. In order to arrive at our desired result, we are going to be subtracting the total material score for Black from the total material score for White. It therefore makes logical sense to first make each of the values in black in this array negative.

Of course, we cannot do this by simply multiplying this array of 10 elements by -1, since we wish the values in red to remain as they are. Hence, we again employ some knowledge of how Excel operates with regard to matrix multiplication and form the product of the above array with the array {-1,1}.

The reason this works is that, if we perform:

{5,5;1,1;2,1;1,1;1,1}*{-1,1}

the result is:

{-5,5;-1,1;-2,1;-1,1;-1,1}

by virtue of the fact that:

{5,5}*{-1,1}={-5,5}

{1,1}*{-1,1}={-1,1}

{2,1}*{-1,1}={-2,1}

{1,1}*{-1,1}={-1,1}

{1,1}*{-1,1}={-1,1}

Our formula now looks like:

=SUM({-5,5;-1,1;-2,1;-1,1;-1,1}*{1;3;3;5;9})

Again, knowledge of how Excel resolves operations regarding arrays of differing displacements is crucial here. In this case, in order to multiply each of the 10 values in the first array by their corresponding piece value, it is not necessary to construct a second array which also consists of 10 values.

Rather, we can take advantage of the fact that we know that both of the entries in each of the “pairs” in our array of 10 elements above needs to be multiplied by the same piece value: the first pair, -5 and 5 (corresponding to the number of Black and White Pawns respectively), both need to be multiplied by 1 (the piece value of a Pawn); the second pair, -1 and 1 (corresponding to the number of Black and White Knights respectively) both need to be multiplied by 3 (the piece value of a Knight), etc., etc.

Hence, if we construct our second array carefully, making sure that it is of the correct dimension, then we can reduce an array of what would have been 10 elements to one of just 5.

In this case, since our first array is a 5-row-by-2-column array, we need to make sure that the other array in our product is a 5-row-by-1-column array. Hence the semi-colons. As such:

=SUM({-5,5;-1,1;-2,1;-1,1;-1,1}*{1;3;3;5;9})

becomes:

=SUM({-5,5;-3,3;-6,3;-5,5;-9,9})

which is finally -3, as desired.

Another challenge to follow shortly. Watch this space!

Hi XOR LX,

This is one of the most interesting Excel posts I’ve ever seen! I love Chess and Excel so imagine how intrigued I was when I read your post! The {“B”,”W”}&{“P”;”N”;”B”;”R”;”Q”})*{-1,1}*{1;3;3;5;9} concept is amazing! I’ve been playing around with it all weekend.

Thanks!

Kevin

Pingback: Video 00162 Excel Formula Calculates Value of Chess Pieces | My Spreadsheet Lab