The Chess Master

 Or "How to qualify for the Microsoft Excel World Championship without being particularly good at Microsoft Excel"

Today's Excel battle, titled "King's Gambit", turned out to be a fine example of the kind of challenge I can do relatively well at despite my lack of really advanced Excel technique and cleverness. So I thought it would be good to write up a walkthrough to give ideas for anyone else who's hoping to do well in these things using only the most basic guesswork and formulas.

Because I did better in this one than I have in any of the cases so far this year - even good enough to get into the second slide of top non-streamed players!

(Four players are streamed live solving the case after the rest of the competitors have finished, and one of them got a higher score than me, so I was 32nd overall today - I've been live-streamed once, and it was really surprisingly nerve-wracking! I'd assumed I'd be fine with it, since I've done memory challenges live plenty of times before, but it turns out it's not quite the same thing...)

But more importantly, there's the slide for the top competitors in the "Masters" category!

Yes, of all the people born in 1976 or earlier ("Mastery" of Excel competitions is a prize for being too old), I was number one, and got my ticket to the online early stages of the World Championship in October!

They've also redesigned the picture I shared last time, to make it a bit clearer, so here's what it looks like now - I'm officially one of the 108 who qualify through the Road to Las Vegas:

So how do I get an impressive 825 points out of a more difficult than usual task? (The top score was 1100, by someone who really knows what he's doing!) Let's go through it, level by level.

In the five minutes before the timer starts, we get to watch a brief video describing the case, and make some last-minute preparations. The video this time didn't tell us much, except that it's about chess, and gave us a glimpse of the instructions and the chess board layouts and notations being used:

I put together a quick list on a blank spreadsheet of board positions (a1, a2 etc) and the Old English Notation of the pieces that start on them, in case such a thing would come in handy, and then it was time to download the case and start the 30-minute timer!

The questions always start out easy and get progressively harder. The first level is a simple request - what's white's 11th move, and so on?

And I used VLOOKUP. There's a running joke among Excel athletes about that extremely popular formula, which has been surpassed by XLOOKUP and other newer, more versatile things, but this is one of those cases where I didn't need to think beyond VLOOKUP, which tells Excel to look ahead a specified number of columns and find what's there.

It needs to look ahead double the specified number, plus 1, since there are separate columns for white and black moves, so it's =VLOOKUP(G71,G71:AL71,G71*2+1)

I mean, you could have done it other ways, but VLOOKUP is still my first go-to in this kind of situation. Quick and easy.

Level 2 introduces us to "long algebraic notation", and asks which piece has moved in each case:

So if it starts with a capital K, Q, B, N or R, it's that piece. If it starts with a lower-case letter, it's a P.

The elegant way to do this would be to use the new-fangled Excel formula REGEXEXTRACT, which can pick out capitals from text strings, and tell it to default to "P" if there aren't any. I'm sure a lot of people went straight to that, but I just did it the quickest way that came to mind, taking the first character using the LEFT formula. =LEFT(G113,1)

And then I manually ran down the list of answers and changed all the lower-case letters to an upper-case P.

It's really not good form to do things manually like that. Easy to make silly mistakes. But this was such a simple task, I just sped through it and did it the quick way. I should probably stop that.

The last easy section is level 3, introducing us to the Old English Notation for pieces:

Look up the name of the piece that starts on the specified square. I started to write a formula to look it up from the chess boards on the other tab, and then I remembered that I'd already written that list after watching the video and before downloading the case! So I just did another VLOOKUP to that list, which I'd added to my handy-dandy 'Alphabet lookups' spreadsheet full of all the other similar lists I've created before these battles. This is the first time one of them has really come in useful...

I did have to quickly add W or B as appropriate to the pieces' names in the list, but =VLOOKUP(G156,'[alphabet lookups.xlsx]Chess'!$A:$C,3,0) still only took a split-second to do.

 Having really raced through three levels, I was having fun with this. But now we move on to the more tricky puzzles, with Level 4. Now we've got a whole string of moves, and we want to know the final resting place of the white queen.

There are a lot of moves in some of these games - the longest of them takes up 214 columns (and I've almost never played a game of chess with more than a hundred moves myself, I don't know about you). So we need to find the last white move to start with a Q, and pick up the final two characters from that cell.

There's sure to be an easier way to concatenate every other column (so as to only get the white moves), find the final Q and pull out the fourth and fifth characters after it, but rather than figuring out what clever formula could do that, I added a whole lot more columns out to the right, after all those moves.

(I remember the days when Excel could only deal with 256 columns, and only went up to IV. Luckily, we've moved on since then...)

So these new formulas (all the way up to column PT or thereabouts), say in every other column =IF(LEFT(G195,1)="Q",G195,"")

That gives us all the white moves of the queen, and there in column HM it concatenates them all together and tells me the final two characters, with =RIGHT(CONCAT(HN195:PT195),2)

 

There's an easier way to get that, all in one formula, no doubt, but I'm happy if I can get an answer in a reasonably fast time without stopping to think about how to do it...

Level 5 complicates things a little bit more - now we want to know which piece has captured the black queen!

This is more tricky, since the algebraic notation doesn't specify which piece was captured, only the piece that does the capturing. But having already set up the formulas for the previous level, I just copied them down and one cell to the right, to establish where the black queen finished up. And then underneath those cells, I put another one, picking out every time when a white move involved a capture on the position that was the black queen's final stop. =IF(RIGHT(K258,3)="x"&$HM258,K258,"")

And then I concatenated all of those, and put it next to the question, back to the left of the spreadsheet, to see what it looked like.

Obviously, this isn't a foolproof method. There were multiple captures on the place where the black queen ended up, mostly, and it might not have been captured at all. But I could see from the hints for the first three questions which one was the correct one to pick, and that the third question must be an "X" for "not captured at all". For the others, I just plucked the final capture on that square, hoping at least some of them would be correct.

Tracing the exact movements of the pieces would have been much more complicated. And as it turns out, 14 of my 20 answers on this level were correct, so that worked out quite nicely.

But now, with time ticking away, I moved back to the 'bonus questions' at the start, to hopefully pick up a few extra points.

The first three are relatively easy. The other two require keeping track of what's moved where all through the game, so are impenetrable to the way I've worked things out so far.

The first is just a question of counting how many times the letter N shows up in the level 1 moves. The second is just a matter of copying the first moves from levels 4-7, pasting them into one column and counting how many times each appears. And for the third, counting how many castling moves there are, I typed in a ridiculously unwieldy formula. We're not about being elegant here, just about doing the thing the first way that comes to mind. =COUNTIF(G197:DI216,"0-0")+COUNTIF(G197:DI216,"0-0-0")+COUNTIF(G241:HF260,"0-0")+COUNTIF(G241:HF260,"0-0-0")+COUNTIF(G293:FE312,"0-0")+COUNTIF(G293:FE312,"0-0-0")+COUNTIF(J337:HO356,"0-0")+COUNTIF(J337:HO356,"0-0-0")

So, with time ticking away (although I actually still had at least five minutes; I always panic a bit too early) I had a look at levels 6 and 7 to see if I could pick up a few more points by guessing the answers. And level 6 seems quite good for that - it wants to know where the black king is at the end of the game:

Actually figuring this out, with the added complication of writing into the formula where the king ends up after castling, would take too long. But the hints for the first three tell us that the answer is in column g, c and c respectively.

Now, it seems to me that a black king is going to end the game, more often than not, in rank 8 where it starts. So I might score a few cheeky points by putting g8, c8 and c8, and then (why not?) copying down c8 into all the other answer boxes too!

And this strategy proved to be the best one! (Well, maybe the second best, after actually solving the question, but hey) - as it later transpired, no fewer than seven of the twenty games ended with the black king on c8! And the first one was in g8 too, so that's eight questions on level 6 I got "correct"! See, this is a way to pick up a LOT of cheeky points, and I almost feel guilty about it, almost.

Level 7 is less hopeful - they want to see the entire movement history of a given piece!

Yeah, I'm not going to do that in anywhere near the time left to me. But hey, I reasoned, there might be some cases where a piece doesn't move at all, so the entire answer will be that piece's starting position!

And I'd already got that lookup list of all the pieces' starting positions, and this time I used XLOOKUP, just to show how up to date I am. =XLOOKUP(LEFT(G337,1)&H337,'[alphabet lookups.xlsx]Chess'!$C:$C,'[alphabet lookups.xlsx]Chess'!$A:$A)

And what do you know? Question 132 does indeed give us a game where the white king's bishop pawn doesn't move all through the game. So I picked up an extra nine points for that one, too! I bet white ended up getting trapped in a back-rank mate, behind those three pawns. I've done that many times myself.

So that's how to get an above-average score, by a fair bit of luck, a bit of quick-thinking, and not all that much Excel expertise! I just hope there'll be a few more cases to come that cater to my unique approach to these things...