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.

Fun Beneath the Sheets

I've been loathed to type anything here for a while, cos it means shunting down that lovely still from Desk Set. But we can't stay stuck in the past forever, now, can we? [cue obligatory Librarian hand-wringing]...

 An empty spreadsheet

My love of Excel is well documented. My disregard for the Google Suite is also quite well attested. At the centre of this Venn Diagram is Google Sheets. At first glance, it's an inferior Excel clone: inferior because there's clearly rather a lot of stuff missing, and because it works in a web-browser (or doesn't work in a web-browser, depending on the browser). But while there's a lot Excel can do that Google Sheets can't, there's also a bit that Google Sheets can do that Excel can't. Here's some of the more entertaining bits.

Query

The most obvious thing is the interaction with Google Forms. Excel is not incapable of harvesting live data but it's bloody hard work. Google Sheets has been designed to be fed this hot, juicy data. And, because of the collaborative nature of Google Sheets, it's also been designed to make it possible to query that data without screwing everything up.

=query() is something approximating magic. Excel's pivot tables excite me immensely (now I know how to use them), but that joy is but a fraction of =query(). =query() will run a search query on your data and then produce a whole new array based on what it finds. The SQL syntax it uses takes a bit of getting used to, but once you get the basics you'll find yourself building wonderful query engines. Google's =query() bible explains the basics, but doesn't even begin to express its greatness. Imagine you've got a spreadsheet pulling in data on all tweets ever, but you're only interested in the ones I've made where I use a rude word. You could set up a spreadsheet that queried this master spreadsheet and returned just the tweets by me that say "bum", "knickers" or "trump". You could then play with that to your heart's content. You could even set up an email alert to let you know whenever I tweeted filth.

And because it's Google Sheets, you can even use the query function in a HTML context: Here's a simple javascript webform I built that generates a query function, applies it to a Google Sheet and brings back the results as a webpage. This form is just looking at two columns (date and tweet) and returning the same fields for any matching data), but it could be far more elaborate, and can return any part of the data you want. In this case the form is generating a URL as follows:

https://docs.google.com/a/york.ac.uk/spreadsheets/d/

[ID OF THE SHARED SPREADSHEET]/

gviz/tq?tqx=out:html&tq=

select+D,F+where+D+starts+with+'2014-12-[DATE]'
+and+upper(F)+contains+upper('[FREETEXT]')

...so it's bringing back columns D and F of my sheet for any row where the contents of D starts with "2014-12-"... and the entered date, and an uppercase version of the contents of F contains an uppercase version of what was entered in the freetext field. 

The spreadsheet equivalent of this (having created a form whereby the date goes in A2 and the freetext goes in B2) is:

=query(Sheet1!A1:J583,"select D,F where D starts with '2014-12-"&A2&"' and upper(F) contains upper('"&B2&"')")

Join, Split, Unique and Sort

Before you go formula-blind, let's jump from =query() to something else quite fun. I had a spreadsheet containing a list of names (sometimes more than one name in the same cell, comma separated), and I wanted to collate them into something useful. In Excel, I'd have to mess about concatenating, substituting, lefting, righting, mid-ing, or even reprocessing the data (CSV-style) using the "Text to Columns" tool on the Data tab. But Google have come up with a new pair of functions: =join() and =split() which have the benefit of being something you can apply in a formula rather than a stand-alone gadget. They do exactly what you might expect, and save an awful lot of effort.

My list of names was in column A, so I cobbled together the following:

=sort(unique(proper(transpose(split(substitute(join(",",trim(A2:A)),", ",","),",")))))

First of all I'm trimming my fields to get rid of any trailing space characters.

Then I'm joining the trimmed cells using a single comma to delineate them. =join() is basically a posh concatenation.

Next I use substitute to get rid of any space characters that may have been entered after the commas in the cells that contained multiple names. I could potentially rely on trim again later, but this seemed as well as anything. It's given me a single cell full of names, each separated by a single comma.

Now I need to break this cell up to give one name per cell, so I use =split(): it will transform any comma into a cell division (much as if you were to open a CSV file in Excel).

The cells run across the row, but I want them all in a single column, so I transpose the array.

Some of the names have been entered entirely in lower case, so I use =proper() to change them all to Proper Case.

My list of names is looking good, but now I want to weed out the duplicates. Fortunately, Google have considered that too: =unique() will return only one of everything!

Finally, I pop all the names in alphabetical order by first name using another flash Google function: =sort().

The result is a neat, organised list of names. If I wanted to sort on the surname I've got problems, of course, and I've lost the capitalised second "C" in McCoy. There are things I can do to deal with these issues, but we're going to need a bigger spreadsheet. Let's save that for another day.

The Advent Calendar

At Christmas I built a spreadsheet Advent Calendar. Here's how it worked:

Each cell on the home sheet references an equivalent cell on a hidden sheet. This was not the original plan, but a flaw of Google Sheets is that it loads a snapshot view of the sheet before it updates the formulae, and unfortunately, the snapshot it took was for the end of the month, when the picture was at its most complete! This was quite a flaw, hence the dramatic rebuild.

The principle taking place on the hidden sheet is really rather simple: each cell has a character which maps to a conditional format. So "A" gives the wall colour, "2" gives the green of the grass and the trees, etc. A basic conditional formula checks the current date and displays the appropriate character: for instance, V30 (a patch of grass) has =if($B$39<17,2,""), so is green before the 17th and white thereafter (B39 contains =day(now()) to give the day's date).

There are a couple of more-complicated things going on: 

The sky has =if($A$38="N","H",3), relying on A38's =if(hour(now())>16,"N", if(hour(now()<8),"N","D")). This changes the sky colour depending on the time of day. I'd've liked to have added more shades to this, but the snowflake glyphs limited the conditional formatting options available. 

The Christmas Cards use Google's =image() function (e.g. image("http://www.avwoman.co.uk/advert/skaterx.jpg",2)). This is another place in which Google has the upper hand over Excel, as it makes a formal link between an image and its cell location. It's still a bit more limited than would be ideal (I'd've loved to have put an animated .gif in the fireplace, but that's beyond its capabilities), but it's a massive improvement on the mess you get in Excel.  

That, then, is an overview of some of the fun you can have with Google Sheets that you can't have with Excel. For a full list of Google Sheets functions, see this full list of Google Sheets functions. 

• Example sheet for the functions examined above
• 2014 Advent Spreadsheet

Hollywood Answered the Google Question in 1957

That big Chock-A-Block wannabe there is EMMARAC, the new computer that's going to revolutionize the reference library of the Federal Broadcasting Network. The librarians looking at this great machine are about to get the pink slips from payroll that indicate their impending redundancy. The entire library has been rendered as machine code and fed into the computer, enabling it to retrieve in seconds an answer that took the woman in the black and red several weeks to ascertain.

This is a still from the 1957 film Desk Set, written by Phoebe and Henry Ephron from a 1955 play by William Marchant. It stars Katherine Hepburn as a librarian (which is kind of like looking in a mirror for me, as you can imagine) and Spencer Tracy as the efficiency expert re(/dis)organising her library and her life. A Kat Hepburn film is seldom a bad thing, and this is no exception to that rule. Indeed, it is rather lovely. It's got Katherine Hepburn and librarians: how can it fail to be rather lovely?

But let us get to the point of this post: The Google Question. It is an assumption among some people out there in the wild (sometimes even people in government) that the library and the librarian have been made redundant by Google. We, of course, know this assumption to be flawed: even ignoring the limited range of Google with respect to the Deep Web; even casting aside the slightly privileged assumption that Google and its fruits are an affordable and available presence in every home; the truth is that Google is only as smart as the person at the keyboard: to get the right answer, one sometimes has to ask the right question.

And this is precisely where EMMARAC fails too. For example, when asked a question about the King of the Watusi, EMMARAC's best effort is a film review of King Solomon's Mines. By way of comparison, Google also resorts to a film as first guess (in this case 1959's Watusi; it's not until the 9th hit that King Solomon's Mines turns up; oh, and Desk Set is the third hit). To get an informed response, one has to know a little bit about what one is typing about. In most day-to-day cases that may be easy enough. For everything else you can call upon your local Information Professional. Google is an amazing tool but getting the best out of it requires knowledge and skill.

Desk Set reaches the same conclusion about EMMARAC. This machine is not a replacement for librarians: it is a shortcut; a new index to consult when required. There will be no redundancies in this reorganization (indeed, a new business venture may, conveniently, even require some extra staff). The panic subsides and everyone lives happily ever after. Or more likely they spend the next fifty-odd years continually reproving their worth as further technological efficiencies are introduced.

Librarians are not, and have never really been, in a war with Google; rather their adversaries in this case are those who think that Google renders librarians obsolete. Here's 103 minutes of half-a-century-old gentle romantic comedy that clearly and relevantly demonstrates the shortcomings of that notion. Not only that, but they have the sort of office parties to which we should all aspire.

 

 Trailer (contains spoilers)

[The full version of Desk Set is available, pay-per-view, on YouTube,

or from your local lending library]

A brief web-history of time-wasting.

Last year, in another bean-counting exercise, I gave you my top 40 most-visited sites of 2013. Now it's time to give 2014 the same treatment.

Before the table, the necessary caveats: This list is just the websites I visited using the Opera 12 browser on my home computer in 2014 (there's a start date of Jan 13th, so there's a couple of weeks missing from this time last year). In the last few months I've been using IE for Tweetdeck as it no-longer works properly in the old Opera. This means that the tally below is missing about a quarter's worth of twitter-derived traffic. As Opera 12 gets increasingly past it, I've also been using Firefox and (more recently) Pale Moon for certain things (not least these 'blog posts). But the vast majority of my home webbing is through Opera and here are the results (hover over the site names for context):

			Total (h:m)	Daily av. (m:s)		%
		OVERALL	43d 01:19	02h 35:38	-50:22
1	=	tweetdeck.twitter.com	10d 03:02	41:19	-21:38	26.5	-4.5
2	=	en.wikipedia.org	5d 13:31	22:31	+10:41	14.5	+8.8
3	NE	feedly.com	3d 14:30	14:32		9.3	+3.6
4	NE	games.usvsth3m.com	2d 14:21	10:36		6.8
5	=	www.youtube.com	2d 06:19	09:14	-00:11	5.9	+1.3
6	▲1	www.bbc.co.uk	2d 00:02	08:10	+01:20	5.2	+1.9
7	▼1	www.facebook.com	1d 18:17	07:11	-01:29	4.6	+0.4
8	=	www.google.com	21:41	03:41	-02:27	2.4	-0.6
9	NE	www.dailymotion.com	15:49	02:41		1.7
10	NE	veritypodcast.wordpress.com	15:29	02:38		1.7
11	▼7	www.blogger.com	15:04	02:34	-07:14	1.6	-3.2
12	▲2	www.avwoman.co.uk	14:25	02:27	+01:08	1.6	+1.0
13	NE	gabrielecirulli.github.io	12:58	02:12		1.4
14	▼4	twitter.com	12:47	02:10	-00:50	1.4	-0.1
15	▲9	lawrencemiles.blogspot.co.uk	12:17	02:05	+01:33	1.3	+1.1
16	NE	thedoctorwhopodcast.com	12:11	02:04		1.3
17	NE	www.radiotimes.com	09:57	01:41		1.1	+0.7
18	▼7	localhost	09:39	01:38	-01:10	1.1	-0.3
19	▼16	theoldreader.com	07:40	01:18	-10:31	0.8	-4.9
20	=	www.sporcle.com	05:16	00:54	+00:10	0.6	+0.2
21	▼12	maps.google.co.uk	05:00	00:51	-03:54	0.5	-1.8
22	NE	www.british-history.ac.uk	04:29	00:46		0.5
23	NE	vacancies.leedsmet.ac.uk	03:19	00:34		0.4
24	▼7	jobs.york.ac.uk	03:11	00:32	-00:21	0.3	-0.1
25	NE	www.fchd.info	02:52	00:29		0.3
26	NE	www.chambers.co.uk	02:49	00:29		0.3
27	NE	usvsth3m.com	02:34	00:26		0.3
28	▲9	www.imdb.com	02:34	00:26	+00:10	0.3	+0.2
29	NE	www.buzzfeed.com	02:22	00:24		0.3
30	NE	www.librarything.com	02:20	00:24		0.3
31	NE	www.dalek6388.co.uk	02:18	00:23		0.3
32	NE	planningaccess.york.gov.uk	02:15	00:23		0.2
33		www.channel4.com	02:15	00:23		0.2
34	NE	docs.google.com	02:11	00:22		0.2
35	▼19	www.theguardian.com	02:00	00:20	-00:43	0.2	-0.3
36	NE	tardis.wikia.com	01:55	00:20		0.2
37	▲2	www.york.ac.uk	01:53	00:19	+00:05	0.2	+0.1
38	▼25	www.splendidchaps.com	01:40	00:17	-01:02	0.2	-0.4
39	NE	twominutetimelord.com	01:37	00:16		0.2
40	▼11	knowyourmeme.com	01:34	00:16	-00:09	0.2	=

The highest New Entry is Feedly: it's been a torrid time for RSS ever since Google did evil by killing its reader. My original replacement, The Old Reader, slips 16 places as a consequence of a decision to limit the number of feeds served by the free package. So Feedly is my new home. I've had no problems with it. 

Another big faller is The Guardian, though only by 43" a day, so it's hardly a crisis. Worth noting, though, is the appearance of web magazines Buzzfeed and the Mirror's usvsth3m. I've kept the latter's games domain separate from the rest, as that's basically there as a consequence of the things that inspired my 2048 and Fight! simulators.

Sites involved in last year's grand mapping project have given way to sites about Doctor Who as the combined efforts of last year's Splendid Chaps and the arrival of Peter Capaldi in the title role kindled a taste for Doctor Who podcasts of various flavours (not least the amazing Verity!, new in at number 10).

There's a lack of retail outlets this year, which is something worthy of note (the first turns up at 41). Instead there's a few job application sites (yawn). YouTube has some competition from the rockier shores of DailyMotion, and I've started getting my TV listings from Radio Times rather than TVCatchup (so as to limit to Freeview channels). But that's pretty much all there is of note, except that as a trained librarian I've now got my googling time down to under four minutes a day, like the search ninja that I am.

I'll keep keeping a tally through 2015, though whether Opera can last that long remains to be seen. For now, though, that's quite enough stats.