1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Excel Challenges' started by Peter Bartholomew, Mar 31, 2018.

  1. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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.

    Attached Files:

    NARAYANK991 likes this.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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?
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
    Peter Bartholomew likes this.
  6. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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.
  7. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    478
    Hi, to all!

    This could be shorter ( 1 x 2 array formula ):
    =MMULT(TRANSPOSE(piece.value),COUNTIF(board,color&piece))

    Blessings!
    r2c2 and Chihiro like this.
  8. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    478
    And, with PowerQuery, this could be an option. Blessings!

    Attached Files:

    GraH - Guido likes this.
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,919
    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).

    Attached Files:

    GraH - Guido and John Jairo V like this.
  10. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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.
  11. r2c2

    r2c2 Active Member

    Messages:
    149
    @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.
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,919
    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.

    Attached Files:

  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    @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.
  14. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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.
  15. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    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!
    NARAYANK991 likes this.
  16. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    369
    Attached: An unprotected workbook with a few additional notes.

    Attached Files:

    NARAYANK991 likes this.

Share This Page