• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Evaluate the material on a chess board

P

Peter Bartholomew

Well-Known Member
The exercise is to evaluate the material on a chess board at a given point during play.
In the attached workbook the lower part of the sheet contains the input range 'board' that defines the current position and a set of array constants that specifies the scoring scheme.

The objective is to create an array formula that will calculate the material value for both white and black pieces (as shown in the upper part of the sheet). By way of interest, the coloured version of the board is also drawn through the use of a single array formula.

If you would prefer an alternative challenge, you might like to consider the same problem but solved using Power Query (Get and Transform) instead of array formulas. This is shown on the second worksheet which also contains a table with the scoring scheme.

Note: The attached workbook contains possible solutions to the problem but is lightly protected so that, hopefully, it will not detract from the challenge.
 

Attachments

  • chess values.xlsx
    30.5 KB · Views: 27
Hi ,

These seem to work :

Chess board images :

=IFERROR(INDEX(piece.image, MATCH(RIGHT(board,1), piece,0), MATCH(LEFT(board,1), color, 0)), "")

using CTRL SHIFT ENTER in an 8 x 8 matrix.

Material values :

=SUMPRODUCT(IFERROR(INDEX(piece.value, N(IF(1, IFERROR(piece.input, 9)))),0)*(piece.color = 1))

using CTRL SHIFT ENTER.

Changing the highlighted value to 2 gives the material value for black.

Narayan
 
Hi, your chess board image is created in much the same way as mine. I used ISBLANK rather than ISERROR but, other than that, nothing to choose.
Our calculation of material values is different in that I calculate the two values within a single array formula, building upon the outer product array
= player&piece
which evaluates to {"WK","BK";"WQ","BQ", ... etc}
That said, your formula works perfectly well and demonstrates the use of coercion of the INDEX function nicely.

Perhaps you might advise me as to the preferred timing of any disclosure of the method within the workbook for comparison and discussion. There might be others prepared to have a crack at the problem?
 
Hi ,

Since you have posted the challenge over a weekend , I think Tuesday or even Wednesday may be a good enough time to disclose your workbook details and throw open the question to discussion.

Narayan
 
Hi ,

For the material values , would this do ?

=MMULT(--TRANSPOSE(ISNUMBER(piece.value)), COUNTIFS(board, color & piece) * piece.value)

entered using CTRL SHIFT ENTER into a 1 x 2 vector (array).

Narayan
 
NARAYANK991 said:
Hi ,

For the material values , would this do ?

=MMULT(--TRANSPOSE(ISNUMBER(piece.value)), COUNTIFS(board, color & piece) * piece.value)

entered using CTRL SHIFT ENTER into a 1 x 2 vector (array).

Narayan
Click to expand...

Hi Narayan
That meets the spec perfectly. I find MMULT to both robust and efficient though, unlike VLOOKUP, it isn't exactly the nation's favourite! I used the problem to demonstrate a slightly different trick with INDEX but we can come to that in due course.

I wonder whether we have any Power Query gurus on board to tackle the alternative approach? I was expecting to need a pivot table but, in the event PQ was sufficient.
 
Here's another PQ option with output grid.

To minimize data load, input table is referenced and split into 2 separate operation after unpivot. 1 for calculating values and another for chessboard image.

FYI - You may need latest update of PowerQuery/Get & Transform to see steps (it will likely raise some error on Excel 2010 + PQ).
 

Attachments

  • chess values.xlsx
    36.8 KB · Views: 8
I deliberately set a challenge that is about as far from the usual diet of VLOOKUP and fill-downs as I could get. It is exciting to receive such accomplished solutions in return! Since I am a PQ novice (my work never required the analysis of corporate data), I can even set out to learn.
 
@Peter Bartholomew Interesting problem.

My board formula is similar to @NARAYANK991 and others.

=IFERROR(INDEX(piece.image, MATCH(RIGHT(board,1),piece, 0), MATCH(LEFT(board,1),color,0)),"")

My score formula is this:

=SUM(INDEX(COUNTIFS(board, (color&piece)) * (piece.value),,2))

Or this entered in a range of 2 cells
=SUM(INDEX(COUNTIFS(board, (color&piece)) * (piece.value),,{1,2}))

all are array formulas.

I will try PQ option now.
 
Huh, I was looking through the file before archiving it and realized PQ was eliminating 5th row on the chess board since all fields were null at that row.

Here's amended version which first replaces null with place holder to avoid this issue.
 

Attachments

  • chess values_v2.xlsx
    36.9 KB · Views: 5
@Chihiro
I have to admit that I hadn't noticed the new 'compact' board layout! The update is a major improvement because a blank column (resulting from moving a white knight to b5, say, leaving the 'd' file empty) causes the original to error.
 
As I understand the rules, I am meant to have a solution :)
The original book was uploaded with 'chess' as the sheet password.

Chess board image:
The coloured board was created, as an afterthought, using the formula
= IF(ISBLANK(board), "", INDEX( piece.image, piece.input, piece.color ) )
where 'piece.input' and 'piece.color' are indices
= MATCH( RIGHT( board,1), piece, 0 )
= IF( LEFT(board,1)="W", 1, 2 )

Material values:
The key to my getting two material values, one corresponding to each player, was to define the 'non-iterating' defined name 'player' by using the INDEX function
= INDEX(color, player.index)
which comprises a "W" and a "B".

This meant that 'player&piece' behaves as two column vectors of length 6, as does the resulting 'piece.count' which refers to
= COUNTIFS(board, player&piece)

The final formula on the worksheet was
= SUM( piece.count * piece.value )

The key to the calculation is to avoid the use helper cells, which would recombine the two 6x1 arrays into a 6x2 array and so give the material sum of black and white pieces.

r2c2 does the same thing by applying the INDEX operation later in the calculation.
 
Power Query:
The list of pieces remaining on the board was created by unpivoting the board and splitting the piece identifier column by position to give 'Player' and 'Material' columns.

This query was merged with the material value table using a left outer join. Removing columns, I was left with 'Player' and 'Value' columns containing a record for each piece.

My final step differs from the 'Group' step used by others (possibly indicating nothing more than my lack of familiarity with normal practice). What I did was to use the 'W's and 'B's in the Player column to re-pivot the table, so aggregating the Value column, to give the final two-cell table.

Comment:
The thing that fascinated me about this problem was that it seemed possible to use two highly structured but radically different solution processes and apply them to the same problem.

I normally associate array solutions with the accumulation of arrays in which the values are governed by difference equations, whereas PQ, to me, addresses the slicing and dicing of lists.

What I didn't try was the 'normal' squidgy, unstructured world of relative referencing in which content is described, cell by cell, in terms of its location on the worksheet!
 
You must log in or register to reply here.
Back
Top