The Macro Terror

So usvsth3m came up with Fight! which lets you pitch things against other things to generate a ranking of things. One of their Fight!s seeks to rank Doctor Who stories, and I spent quite a while prodding the buttons to see how my opinion fared. Having done that for far too long I decided that the consensus emerging in the Fight! tally was a bit wrong, and I began to wonder what my own hierarchy would look like. I could’ve just scored each story individually but, being me, I turned to Excel.

Vincent & the Doctor vs Robots of Death. Hm.

To start with, I collated my list of stories, went to Fight! and did a spot of simultaneous manual marking just to get some numbers into the spreadsheet. That being done, it was time to rig up an Excel version of the Fight! system: essentially, I need to pick two stories at random (not the same one), and have a button for each story. Clicking on a button adds 1 to the winner’s WIN column and adds 1 to the loser’s LOSE column. Where to begin?

First of all, let’s get the two stories picked at random. That’s easily done. =RANDBETWEEN(1,241) will generate a random number between 1 and 241, and we can have two of these, plus a formula like =IF(J1=K1,IF(J1+1=242,1,J1+1),K1) to make sure that the two (nominally placed in J1 and K1) don’t give us the same number. We can then have the spreadsheet call the stories in question. A simple way is to make use of the row numbers by using an OFFSET formula, eg: =OFFSET(A1,J1-1,0) which will return a value from column A at the row-number specified by our random number in J1 (the OFFSET basically takes a cell reference (in this case A1) and then moves the specified number of places down and across. It’s great for mapping to a value that’s always in a particular place in the sheet).

The trouble with =RANDBETWEEN() ( and with its little sibling =RAND() ) is that the value updates every time we update the spreadsheet, and this is going to cause us trouble when it comes to adding our 1s into two different columns (the first 1 can be added, but then the random numbers regenerate and we lose our bearings). We were always going to need to use macros in this task, but it turns out we need them earlier than we thought. Time to open up Visual Basic and add a module...

At some point down the line we’re going to have two buttons in the spreadsheet, so we can click to vote for whichever of the two stories we prefer. I’m therefore going to write two subroutines: one for each button. These two subroutines will be fundamentally similar so we can build them up at the same time. 

If you want to play along at home, you’ll need to get the Developer menu displaying in Excel. It doesn’t show by default: you have to go into Excel Options and tick the “Show Developer tab in the Ribbon” box in the “Top options...” list. That being done you can go to the Developer tab, click on Visual Basic then Insert > Module to get started.

The magic portal to fun-aplenty

Let’s start with a couple of blank subroutines by typing:

Sub Button1()

End Sub

Sub Button2()

End Sub

Now let’s make it so that clicking one or the other button will generate random numbers in J1 and L1:

Sub Button1()

Range("J1") = Rnd() * 241

Range("L1") = Rnd() * 241

End Sub

Sub Button2()

Range("J1") = Rnd() * 241

Range("L1") = Rnd() * 241

End Sub

Rnd() is the Visual Basic version of =RAND(), and we’re multiplying by 241 as that’s how many stories there are this side of the 12^th/13^th Doctor (well, for the purposes of this exercise anyway).

Look at all those form elements. We're just scratching the surface here.

Go back to the spreadsheet, and from the Developer ribbon’s Controls panel click Insert and select the Button option (versions of Excel differ, of course, just to make life interesting). Make yourself a nice button and right-click to assign a macro to it. Assign Button1 to one of the buttons and Button2 to the other. Now click on the buttons and hopefully they’ll start belching random numbers into cells J1 and L1. 

We still need to ensure that we don’t get identical numbers in both cells and we can do that as we did before. We might also want to use the =ROUND function. I have =IF(ROUND(J1,0)=0,ROUND(J1,0)+1,ROUND(J1,0)) in J2 and =IF(ROUND(L1,0)=0,ROUND(L1,0)+2,IF(ROUND(L1,0)=J2,IF((ROUND(L1,0)+1)=242,240,ROUND(L1,0)+1),ROUND(L1,0))) in L2, just to keep my selections nice and tidy. You can then use the OFFSET formula to call up your stories’ titles.

That’s the easy bit done. Now we need to get the buttons to start adding figures to columns too.

I have my win tally in column B and my lose tally in column C. If the story in row 1 beats the story in row 2, the value in B1 needs to increase by 1 and the value in C2 also needs to increase by 1. With Visual Basic it’s not as difficult as it might first seem.

Sub Button1()

Cells(Range("J2"), 2).Select

Selection.Value = Selection.Value + 1

Cells(Range("L2"), 3).Select

Selection.Value = Selection.Value + 1

Range("J1") = Rnd() * 241

Range("L1") = Rnd() * 241

End Sub

Sub Button2()

Cells(Range("J2"), 3).Select

Selection.Value = Selection.Value + 1

Cells(Range("L2"), 2).Select

Selection.Value = Selection.Value + 1

Range("J1") = Rnd() * 241

Range("L1") = Rnd() * 241

End Sub

The first line here is the Visual Basic version of =OFFSET. The “Select” bit says we’re going to ‘click on’ the “Cell(s)” specified by the coordinates in the brackets: 2 = Column B; Range(“J2”) = the Row specified by the value in J2.

The second line says what to do when we get there: namely that the value of the selected cell should now equal the value of the selected cell plus 1. Quite a neat little line really.

Go back and have a few clicks to see how it works. You’ll notice that your selection ends up somewhere in column B, which is not very helpful if we have to keep scrolling back up the page, so you could go back and add something like:

Cells(1, 14).Select

...before both “End Sub”s, just so that a cell at the top of the sheet is selected. It just gets the cursor back to a sensible place.

That’s the hard work done. Now you can start merrily clicking away and building up the raw data you’ll need to create a ranking. Sticking something like =(B1-C1)/(B1+C1) in D1 will give you a normalised score from 1 (wins every time) to -1 (loses every time) which you can then set about ranking using the =RANK formula. You could then build a dynamic leaderboard. I built mine using a =VLOOKUP: =VLOOKUP(P1,E$1:F$241,2,FALSE) where column P has running place-numbers, column E has place rankings, and column F concatenates the story names from A with the hit stats from B and C. The problem with this approach is that it relies on there being no tied places in your rankings, which is frankly unlikely. There are some elaborate ways of avoiding this kind of thing, or you could do what I did and cheat (I basically beefed up the figures in D and then added a tiny arbitrary fraction (e.g. 0.0000241 for D1, 0.0000240 for D2 etc.) effectively breaking the tie by alphabetical order). Cheating can be your friend for such things as this, but for something more important you could just record yourself a nice, neat sorting macro.

Ok. So everything is lovely. You’re able to call up story titles and vote for your favourite, and get a running ranking as you go along. You can even call up various stats at the same time and conditionally format them to give you a cue as to which story is likely to be the better one. You might also use a deduping condition to highlight a contending story in your leaderboard. But there’s something wrong. Something it may take you a while to notice, but it’s something that is very very wrong...

After a few thousand clicks; a few sessions, perfecting my formulae and my hierarchy; that’s when I spotted it: That’s when I noticed that my supposedly randomly selected pair-offs were becoming disturbingly predictable. You can’t help but notice when you get “Curse of Fenric” vs “Curse of Peladon” a few goes before “Abominable Snowmen” vs “Snowmen”. My random selections were following a defined pattern.

Computers don’t really do ‘random’. It’s not in their nature. Instead they play around, doing entertaining maths with ‘seed’ numbers. The problem I was having was that the seed number wasn’t changing, and every time I made an alteration to my macros and started again I saw some familiar face-offs.

One of the ‘more random’ seeds a computer can use to make its ‘random’ numbers is the clock. Time is constantly changing, from second to second. What we can do is tell our macro to get a new seed from the clock every time it generates a random number. Sticking Randomize Timer as a new line before each of our Rnd() lines should do the trick.

Phew.

Me being me, I wasn’t happy with just having a ranking based on the ratio of wins to losses. I wanted something a little more elaborate. My current fights therefore have more at stake. Each story is allocated simple ranking points (241 for 1^st place, 240 for 2^nd place, etc), and these inform another tally of fighting points: If the winner of a battle has less ranking points than the loser, they trade these in the form of fighting points. So if the bottom-placed story beat the top-placed story, the bottom story would net itself 241 fighting points while the top story would have 1 fighting point. Fighting points therefore effectively represent a sort of scalp trophy. The ranking and fighting points are added together to give an overall ranking. This system allows stories to move much more quickly through the league table and makes the order far more accurate.  The current order still has a few wrinkles, but it’s now a pretty reasonable reflection of my tastes and opinions.

Blink is about to cement its place ahead of Caves of Androzani.

The clicking is never done...

Surely there are better ways of doing this? Alas, a full round-robin would require 28,920 clicks and I've pulled together my current ranking from but a mere 8,131. However, I may have to come up with a way to fix the random number generators to select two stories within, say, five places of each other, just to get rid of pointless battles like the one above.

There you go, then. That’s how to take the fun out of a diverting web amusement. You may rest safe in the knowledge that Victory of the Daleks sits safely plumb-last in my list. Fiat 500 Daleks are not for me.