The Twin Dilemma

A couple of years ago I made a spreadsheet for ranking Doctor Who stories, and then I wrote a post about it (a post with a pleasingly punny title). A couple of years is a long time in spreadsheets.

To recap: this spreadsheet presents two randomly selected Doctor Who stories, I have to choose which is the better of the two, and then magic happens.

In 2014 it looked like this:

Two years and 6869 clicks later and it's looking more like this:

Let's start at the left and make our way across the face of this particular white elephant. You may have absolutely no desire to rank Doctor Who stories, but maybe there's something else you want to rank, or maybe there's something behind the scenes you can nick.

First of all, the master list of stories now sorts automatically after each transaction, thanks to a few more lines in the macro:

    Columns("A:N").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H1:H266") _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort

        .SetRange Range("A1:N266")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

This makes the scoreboard in what is now column G pretty redundant, but the top twelve and bottom five are preserved for easy reference.

The formula fodder that was originally hidden at the right is now moved to the left to keep things tidier - it's quite handy to have the story and Doctor numbers close to the story name.

The way the stories are selected has been amended. Before they were both random picks from the full list, but now the second story is selected from the 25 stories that are ranked immediately below the first selection ( Range("q1") = (Rnd() * 25) + Range("o1").Value ). This makes for closer matches. It also means that the higher-ranked story is on the left, which makes it easier to judge the progress of the ranking (as we go on, I should be clicking the left story more frequently, because the rankings should be increasingly correct).

Another tweak to the randomisation is that the seed is generated from a larger array than the number of stories (255+25 versus 266), and then rolled back by five, to give a potential range of -5 to 275: this gives more opportunities for the top-ranked and bottom-ranked stories to show up and to take on their neighbours.

Column O has an aggregate ranking for each Doctor. This was initially a straightforward average of the positions of their stories, but I felt that this unfairly penalised (or advantaged) those Doctors who had one or two uncharacteristically bad (or good) stories. The current formula is:

=((SUM(StoryScores)-MAX(StoryScores)-MIN(StoryScores))/(COUNT(StoryScores)-2)+((2*MEDIAN(StoryScores)+QUARTILE(StoryScores,1)+QUARTILE(StoryScores,3))/4))/2

In other words, the mean of a topped and tailed sample of ranked stories, averaged against a weighted selection of the median and quartiles of that range. It makes for a fairer ranking of Doctors. Currently, Doctors II, IV and V are busily fighting out for the top places. Thereafter it becomes a little bedded down (especially now the Capaldi episodes are bedding in).

I've also added (simpler) ranking tables for seasons/series, companions, writers and directors. Top and bottom fives of the latter three appear in column T (the fact that Barbara is nowhere to be seen being a clear reminder that we're ranking the companions who got the best run of stories, not the best companions full stop).

Sums for these rankings are performed on Sheet 2. Google Sheets would make the process an awful lot easier: in Excel we're forced to deal with multi-column ranges and manually generated deduplications, while Google could've done the whole thing in a couple of quick columns. But not to worry. What I've got is messy but it works. AVERAGEIFs, VLOOKUPs, RANKs, COUNTIFs, ROUNDs and LARGEs are employed in the process of calculating the rankings and generating the leaderboards.

Finally, there's the graph. This graph:

...the key for which is:

Story ranking

Doctor ranking

Season/Series ranking

Current selections

Most of those lines are pretty straightforward, generated from the same running AVERAGEIFs that create the aforementioned ranking tables. The "current selections" indicators are also pretty straightforward. They're created from a conditional formula whereby the selected stories get their current ranking and everything else gets 270. The y-axis only goes to 266, so the 270s are unseen and all we're left with are the neat little arrows. It's simple but surprisingly helpful.

The ranking is still not perfect, but it's getting there. The shape of that graph seems pretty right, even if some of the particulars are a little out. Even then, things are starting to appear in roughly the right order. Another 15,000 clicks and we might just crack it!

If you disagree with how my ranking is shaping up, you can download a copy of this spreadsheet yourself if you really want, and start clicking away to bend it to your own opinions. I'd be interested to see how long it takes you to turn it around. And if you spot any errors or improvements regarding the mechanics of the thing, do let me know.

Array of Hope

My love of spreadsheets is well attested. Here's another post about them...

I've been using Google Sheets a lot more lately. I love Excel, of course, and there are things that Excel can do which Google Sheets cannot. But increasingly, the things Google Sheets can do which Excel cannot are proving far more useful to me. Some things, like the flexibility of being able to employ close-topped but open-ended ranges (e.g. A3:A) are just convenient little time-savers, but they are emblematic of something very important: Google Sheets is designed to handle growing data sets in a way which Excel was not.

Don't get me wrong, Excel has the capacity to cope with growing data: it's the "Table" option on the "Insert" tab. If you are likely to be continuously adding data to an Excel spreadsheet, and want to ensure that any formulae expand accordingly, then format your dataset as a Table. It's good, but it isn't easy to operate across multiple sheets. Things start to get knotty. Compared to Google Sheets, Excel's solution is primitive.

Google solves the problem by using =ARRAYFORMULA() - If you stick a formula in the brackets and change any cell references to ranges (e.g. replace an A2 with an A2:A), it will expand the formula down the page.

Excel also has array formulae. But in Excel they're a kind of bewildering black magic that involves pressing three keys simultaneously. Learning how they work pretty-much involves several days of macheteing your way through Stack Overflow questions. Save the Blank Skip example I mentioned on this blog, for now we'll ignore these curly-bracketed monsters. 

Google's =ARRAYFORMULA() are just so ridiculously straightforward to use. And they work easily across sheets. You will never have to expand a formula down the page ever again. They are the future of spreadsheets.

Here's an example... Two coins are being tossed. I've done four tosses but I'll probably toss some more later. Column C uses =ARRAYFORMULA() and a nested IF (the first IF gives "" if there's nothing in column A for that row, just to keep things tidy; the second IF converts my tosses into binary):

	A	B	C
1	Coin 1	Coin 2	Coin 1 = Head?
2	Heads	Tails	=arrayformula(if(A2:A="","",if(A2:A="Heads",1,0)))
3	Tails	Heads	0
4	Heads	Heads	1
5	Heads	Tails	1
6

The formula completes down the column automatically. It's lovely.

=ARRAYFORMULA() works with most functions, but not all. AND and OR don't seem to work, and some other formulae will only spring into life if you dress them up in an IF. Things like COUNTIF and COUNTIFS break things because they rely on range statements and that seems to get in the way of the ARRAYFORMULA syntax. But there are ways around such things.

If we wanted to keep a live count of the total number of Heads in column A we could join all the cells in A together, count the length, and compare it with the length were we to remove all instances of the word "Heads":

=arrayformula(if(A2:A="","",(len(concatenate(A2:A))-len(substitute(concatenate(A2:A),"Heads","")))/len("Heads")))

This is a bit of a long winded workaround, but it does the trick. We can even get a COUNTIFS effect. This formula checks for Heads in both columns by joining A and B together with "&" and comma-separating them (&","), before applying the COUNTIF workaround:

=arrayformula(if(A2:A="","",(len(concatenate(A2:A&B2:B&","))-len(substitute(concatenate(A2:A&B2:B&","),"HeadsHeads","")))/len("HeadsHeads")))

Here's a nonsense example, testing the probabilities of coins. As the data increases, so does the understanding of any bias in each coin. Conditional formatting (custom formulae of =$M1="No" and =$M1="Yes" respectively) highlights any combinations that do not fall within ±5% of the expected 25% probability. Try adding more rows of data to columns A and B, to see if you can make the coins appear any more or less biased.

In addition to =ARRAYFORMULA() there's also handy tools like =SORT(), =FILTER(), =UNIQUE() and the amazing =QUERY() that we can use on growing sets of data. If you're likely to be importing fresh data-sets, you could set up a SORT and FILTER (or QUERY) formula on a second sheet to rearrange the data into something more useful. Things like this are doable in Excel, but they require you to click buttons. That Google does all of this in the formula bar, and automatically, is what makes it my tool of choice for anything that's likely to grow. The only drawback is that Google is a bit slow. The more data you throw at it, the more cups of coffee you'll need to go and make. But I can live with that.