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.

My Struggle: The First Decade

The Summer of 2015 marked the point at which I'd spent ten years working in libraries. It's looking like it might have the makings of being a career. Back in 2005 I took my first library job (not counting my stint as the 'school librarian' in Junior School) as a Shelver at a teacher-training-college-turned-university in Sheffield. Work was three hours a day, four afternoons a week. It was my first steady job after self-employment and some casual invigilation. 

[2005] I sent an email to enquire about the job, but I didn't have the time to write an application for some reason so I decided not to bother. I was invited to an interview anyway, where my inability to communicate effectively gave my would-be-boss, Bernard, the impression that I would be unable to fulfill the hours (I emailed him afterwards to clear up the ambiguity, which was perhaps as well). I got the job.

Prior to this I'd speculatively applied to work at a number of public libraries in the area, thinking it might make a nice job, but early on I'd made a bad impression with the woman on all the interview panels. It was probably a lucky escape considering what would happen to public libraries in the decade that followed.

Shelving was fun - delightfully physical but not too physical. Short hours which suited my sleeping patterns. I got into it and did a good job. After a few months I was offered the chance of promotion. I made my application and got an interview.

Bernard's opposite number at the other campus (also on the panel) described my interview as the worst he'd ever seen. But competition wasn't a thing in those days and my boss spoke up for me. Such it was that I was now a General Assistant, working four hours a day, five afternoons a week. I had made it.

 General Assistant's Eye View

[2006] At first, being a General Assistant was not entirely dissimilar to being a Shelver, except you got to sort your own trollies before you shelved them. But there were other strings to the General Assistant bow, and more were added with each passing year. A big part of the role was processing: checking the shelfmark allocated to new stock, printing its label and attaching it to the item along with any other Library markings as required. 

There were also certain reprocessing projects, complete with extra hours (full time work!). And then there was repairs. I liked repair jobs. Saving dying books, often by ripping out their spines and rebinding them. Plus the glue smelt wonderful. I endeared myself to my third boss, Karl, by doing a good repair job on the family cookbook. So it was that I was sent off to another Library to help get through a repairs mountain they had there owing to a member of staff being off sick.

[2007] The other Library in question was an Arts Library housed in an old Blue Coat School (site of reputed hauntings) attached to some of the best concrete in Sheffield. It was Heaven, alone with my glue-pots (pictured in the cabinet below) and the most interesting books the University had. I could play conservator with wild abandon and gaffer-tape (it was all that was to hand). The backlog was reduced and more brownie-points were earnt.

My base of operations in the Arts campus years

[2008] Then they decided they would shut down the lovely Arts campus, and after my initial success there I was invited back to help with the dismantling (complete with yet-more extra hours). This included the chance to help work out the shelving allocations at the other end, and to supervise the removal men accordingly. Plus I got to wallow in the Special Collection, where all the very nicest things lived, and to hang around with the Art Librarian, Kate, who was well cool. She introduced me to the wonders of Librarianship, and to the realisation that Library Schools were a thing.

 How to repurpose an abandoned Library

I look back very fondly at those bitter-sweet months in the Arts Library. And I miss that campus so much. They turned it to rubble but not before we had a disco in the empty Library (see above). The stock moved to the big Library on the main campus and I went with it for a time, to help rehouse the Special Collection in its new climate-controlled home.

 The Special Collection being rehoused

My old History teacher, Anna, worked at the city campus, doing the same job as me as her post-retirement earner. She was shocked that one of her star pupils was bumming around rather than making something of themselves, and she gave me a stern talking to. At the same time, I had my appraisal with Karl -- the only appraisal I ever had that was worthwhile -- in which the notion of Library School or Archives School was mooted. I decided to give it a go.

[2009] Archives School was in Liverpool, but there was a Library School in Sheffield, so that was that decision sorted right there. Eventually I made my application, which got through at the last minute, and I attended the last round of interviews for the next year's course (2009/10). I didn't give it much thought, and it wasn't until I started that I realised there had been a chance that I might not have made it through the process (i.e. that some people didn't). But my interview seemed to go ok. I said I'd applied to Sheffield because it was nearest, and that I saw it as an opportunity to sit the recession out (ha!). My application made reference to Giles from Buffy, naturally.

 Legoland Sheffield

[2010] I attended Library School part-time, while continuing my work as a General Assistant, and my second/fourth boss, Julia, was brilliant with regard to my flexible working. By this point the General Assistant role had evolved even further. The Library was now predominantly serving a Nursing course and we were manning what was effectively a second help-desk in all but name, as well as fixing the printers, and supporting the more senior Library Assistants with their back-room work: a range of clerical duties from inter-library loans, collections and serials etc. On account of being the 'clever' one, I was also given the job of leading our role-profile review in an attempt to get our grade upped -- it was never going to happen as it would've set the whole house-of-cards tumbling, but it was a worthwhile exercise non-the-less, not least ahead of all those job applications. To the same end I started to get in some work shadowing with the Librarians about the place. This would be essential when it came to my first professional role.

[2011] After two years of Library School I started sending out applications. I was in no great hurry because I had the security of my General Assistant job, so I started out with the wish-fulfilment stuff: rare books collections... Oxbridge Libraries... one of them even demanded a hand-written letter of application, which was just downright weird (plus my wrist ached for about a month afterwards).

[2012] And then, in the middle of 2012, I saw a maternity cover job in York. I'd been an undergrad in York and I hadn't wanted to leave. Plus it was a Health Sciences / Medicine Assistant Librarian job, which was the field I was currently in. So I fired off a quick application, making a few gambles with my life in the process, just to see what might come of it. And I got offered an interview.

The interview went well. I was on it that day. I'd seen a magazine in the waiting room that had an article of relevance that I could drop into proceedings, and that doubtless helped. But I didn't get the job. What I did get was the offer to do three months' sick-leave cover for one of the Librarians, and I snatched it. I had no idea where I might live, but I had a job. In a different city. For three months.

 Obligatory book shot

Savings and parents were employed to pay out six months of rent, on the big gamble that I might eke out another three months in the job. In the end I got four months out of it. And I was working as a Liaison Librarian in Social Work and Women's Studies. And it was great. I loved it. I had indeed made the right choice. But then it ended; a month after it was initially meant to end, but it ended all the same. There just wasn't any more in the kitty. So what next?

[2013] There was an Open Access job at York but my heart wasn't really in it, and I didn't get it. I had a couple of interviews down south before a colleague pointed me to another maternity cover role, this time over in Hull. I applied, and as with the original York post, I must've been on form in the interview, because it seemed to go ok. And again, I got the job. Third time lucky. I moved to Hull in a Liaison Assistant role. I was concerned that it was technically a backward step, and that perhaps I ought to look to a fully-fledged Librarian position, but needs must. And it was a good time. I was able to put to use various skills from York, and develop some serious spreadsheet wizardry along the way. But it too was temporary cover. And it too came to an end.

 I didn't actually work in here very much.

I was in a terraced house.

And then I got a call to return to York - phew! Yet more cover was needed, for the same job I'd been interviewed for a year earlier. I'd dropped on. Hurrah!

So back to York, a year after my first stint. It should've lasted until Christmas. And it did. This time I was a Health Sciences Assistant Librarian: lots of one-to-one tuition with nervous nurses. But in January we were due a restructure, and that job was to come to an end. Fortunately, another job has come up as an Academic Liaison Librarian for Health, Psychology and Social Policy & Social Work. It looked terrifying, but it seemed to have my name on it. I put in my application and duly got an interview.

The interview was a disaster from the offset. I'd planned the whole thing -- all my examples -- with respect to an academic on the panel. But he wasn't there -- the academics were on strike. And without him, all my planning went out of the window. It was a mess...

...or as I see it, a narrow escape. In hindsight, I'd've hated the job. Sometimes fate knows what it's doing.

[2014] But for now, I had a problem. My plans had collapsed. Fortunately, another hole had emerged, as Assistant Librarian for the Medical School. I got to cover it for a few months before it would be up for interview. But that was only half time. The other half I was able to fill with odd-jobbing for now: covering any cracks while the reshuffle bedded in. I was clinging on for dear life.

Another job came up, looking after software licences. I wasn't overwhelmed by the prospect, but I kind of had to go for it. I didn't get it. An e-learning job came up at another University nearby; I interviewed for that; I didn't get it. Another interview back where I started, which I also didn't get. Again, these are jobs that if I'm honest I wasn't really that bothered about getting, save for the need for food and shelter. It was no fun. It was disheartening. It was worrying.

And then came the interview for the Medical School job. If I messed this one up, that was that. You'll be pleased to know that I didn't mess it up. It went Just Fine. It was easy, lovely, and largely without hitch. Thank goodness for that. Half of my job was now permanent. And it was bloody brilliant too. I even got to work with my mates from Hull from time to time.

Meanwhile, the other half of me had moved into the Teaching & Learning team, to cover a sick-leave which became a retirement. I got to play with spreadsheets and design teaching. I now had two good jobs for the price of one. Time wore on, and I clung on.

[2015] A year later, I was able to be offered a full-time job in the Teaching and Learning team, at Librarian level. It meant leaving the Medical School post [sad-face emoticon], but it was a step up and a secure position, so that was that. That was that and here we are: Ten years after this journey began... my first full-time Librarian post. Or rather, Teaching and Learning Advisor post. *breathes*

Action shot: I teach. It learns.

[2016] So what is a Teaching and Learning Advisor? Does it involve books at all? Well, these two questions, and more, will be the subject of another post on another day. For now, just be content with the knowledge of a job well done and another blog post in the bag.

Cheers to everyone on the journey. And good luck to all. We Librarians... I think we've got nine lives and we land on our feet, just like Shakespear's Sister. I hope you land somewhere nice too.

A series of post holes?

Over nine months have passed since the last post here. Nine months. No, I've not been pregnant. Not even with ideas. There were moments when... well... 

There were moments when I came close to a post. But moments, they pass. Fleeting moments like CILIP eating itself, CILIP not eating itself, and the rest. I think we've all had our fill of eating CILIP. It leaves a nasty taste. Enough of that. I raise a toast to CILIP for at last joining us at the table, though I'm afraid there's nothing left but cheese and biscuits.

 

Not a Library (anymore)

Don't get me wrong. Cheese and biscuits is/are lovely. But, well, you get the metaphor. 

I thought about maybe talking about what I've been up to at work, now that I've finally settled down into a state of permanence. Or how I got to that point. There's a draft post from last August. I might yet finish it off. We'll see. But I didn't at the time because I was too busy juggling the universe.

Then I was going to blog about spreadsheets, but I realised that I'd already said everything I was planning to say in previous posts. So in the end I didn't.

And, then New Year came, and I usually impress you all with some meaningless statistics, but Twitter took two months to get my tweet logs to me, and I don't have a record of my browsing habits anymore, and anyway, it's all just an excuse to postpone the inevitable demise of this blog: all just a ruse to keep the numbers up. 

 Token graph of all my tweeting ever. See also here and here.

Wait - did I just use the words "inevitable demise"? Yeah, sorry, I've a feeling I did. Well, let's not rush to the gallows just yet, eh. As the graph up there shows, I tweet even more than ever, and that is partly a function of my blogging less. The two are not unrelated. But there's more at play.

In 2013, Google killed Google Reader: the most-read feed reader on the internet. RSS feeds had revolutionised the way in which I engaged with the web. They almost killed the browser bookmark. And then, for some reason, Google rid itself of this interminable feed. I still use a reader (Feedly), and get a massive amount of my web content through it, but not as much as I did. Not as much because one by one the feeds are running dry. There's still an impressive stream, but it was once a mighty river.

There's another aspect to this equation. This is not all Google's fault, by any means. A fire will burn itself out if you do not continually add fuel, and fuel takes the form not only of blog posts but also of blogs. This blog was born of Library School, and so were the blogs I followed: the blogs of my fellow students. As we all graduated, got jobs, hobbies, new relatives, and other demands for our time and energy, so the pitter of tiny keys became decreasingly heard. For a time I was fully switched on enough to keep adding more blogs to the fire, as new Library Scholars sashayed into Twitter with their exciting ideas. But blogging is old tech, designed to be distributed through a mechanism that, as a consequence of certain machinations, is no-longer straightforward. Maybe all the big ideas are happening elsewhere, and I'm missing them, or maybe they're happening right here and I'm still missing them. I'm not sure.

On top of all of this, there's what I shall call 'Library Camp Fatigue', for want of a nicer term. A few years ago, my generation of library types stumbled upon the Library Camp: a talking shop for sharing exciting ideas and/or moaning about idiotic ones. There were Library Camps everywhere. They were like free festivals for cake-fetishists. It was amazing. But after a while, I think we got in a rut... talking the same old talk... watching, helplessly, as the fabric of our profession got destroyed. This generation - the tweeting, Library Camping, blogging generation - with fire in our eyes and cake in our bellies - we got tired. Or we got jobs. Or we lost jobs. Or we had better things to do, like fight to keep our jobs, or have a nice quiet weekend after a busy week of doing our jobs. In short, we got old: we weren't students and new professionals anymore. We had other things to do. Some of the greatest bloggers of my generation are too busy looking after the kids/cats and working on home improvements to spend time pissing into the echo chamber. And that is Absolutely Fine. It's my defence too.

This blog is only four and a half years old. It's been a long four and a half years... An eventful four and a half years. Much has changed in that time. Blimey, much has changed. And I'm not packing up just yet. But perhaps I need to refocus a little. And perhaps I need to go away and finish that post I started last August.

And maybe I need to try harder to find the thoughts of the next wave of Library minds too. But first there's some cheese and biscuits to polish off.