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.

2014 in bellybuttons

So this is New Year's Eve, and what have I done? Another year over, in which I made nine 'blog posts (eight if you ignore this one) - half the number of last year's tally. What's gone wrong? Is the 'blog dead??

The first two posts of the year were my usual navel-gazing stat-counts. I followed this up with a post about Excel functions. Then in March I had a spring-clean and the site got a new name. I claimed in that post that the next job would be to sort out my increasingly creaky website, which has sat pretty-much dormant for the last two and a half years (coincidentally the same amount of time I've been in a proper full-time job since I graduated, all those moons ago... what are the odds??). I have not lived up to this claim: I have done nothing to the site.

What did I do instead? In April I unleashed my Excel version of 2048 upon an unsuspecting world. I count this among the year's greatest achievements. In May I put up a PowerPoint slide (again, more showing off disguised as a helpful hint), then in July I did another Excel-related post, showing how to build your own spreadsheet version of usvsth3m's Fight! tool. This is what I had become.

Then, to prove I still had some connection to libraries, I had a good moan about CILIP (as is tradition). And that, ladies and mesters, was the last peep for five months.

This is the point where I should excuse my laxity by announcing that I've been writing a novel. But I've not added anything to that since March. Alas, the first half of the year was rather taken up with job applications, and (family aside) nothing destroys free time quite so much as those things. Free time and creative spirit. 

By the time that job app season was over, I was hard at work at work on various website and spreadsheet related gadgets, and the thought of carrying that stuff over into my leisure time didn't exactly appeal. So I watched a load of Doctor Who, listened to a lot of podcasts and generally vegged out for a bit. Frankly, I needed a rest.

I interrupted my resting for two Library Camps in the back end of this year (one in Leeds, and the national one in Newcastle), both of which were fun, and neither of which I wrote up (though I did do some live tweeting). Likewise, where's my entertaining spin on Sieghart? I could've had you rolling in the stacks with that one, I'm sure! But I didn't. Mainly because I felt I'd be rehearsing old posts (in much the same way that Sieghart said nothing very new). I didn't want to add to the water-treading... it would just have become a tedious tirade against lazy journalists, and given how much I've failed to write this year, my house is too well glazed for stones like that. 

The 'blog isn't dead, though. It's just resting. And while it rests, I'm still twittering away like nobody's business. 

Or am I...

Let's take a look at the evidence!

Total tweet tally for 2014: 6,000 (up from 3,787 in 2013). That's an average of 16 tweets a day (up six on last year).

5 RTs per day (up three) and 6 @s (up two). One contains the wordstem "libr*". Pulling that data out to a week, we get a table like this one:

	2012	2013	2014
Tweets/week	43	73	115
@s	21 (49%)	29 (39%)	40 (34%)
RTs	8 (18%)	17 (24%)	35 (30%)
libr*	3 (8%)	4 (6%)	9 (8%)

My proportion of @s is down slightly. My RTs are up by roughly the same margin, and my library tweeting has more than doubled in real terms. Well that's probably something at least?

Enough waffle. Let's cut to the word cloud!

  

  
When I'm not retweeting, I seem to be talking to @greebstreebling an awful lot, and the presence of @r4isstatic, @JMFrankland and @GrevilleTombs puts the brunt of that chatter at several Sundays spent watching Doctor Who on UKTV Drama. Had @ned_potter not changed his twitter handle mid-year (obviously inspired by my own rebranding), he would be up there too, proving that I'm more likely to tweet to someone than to shout across the office.

Library is down there in the bottom right, smaller than last year, and about the same size as Eurovision and Strictly. There's also a Libraries above @Girlinthe's head. The popularity of amp is actually due to my use of the ampersand, but I can see I've been getting to have the odd bit of a think now and then. This wordcloud doesn't include any tweets I've done this evening, but I doubt they'd alter things too drastically.

Peak tweeting this year was in July when I clocked in 712 (23 tweets a day), in part the result of me live-tweeting the Commonwealth Games opening ceremony (an evening I had completely forgotten about until I did the research just now), partly also #UHMLG14. My twittering low came in April, when I only managed 228 tweets (8 per day), presumably because I had better things to do.

So that's the story of my 2014... a story of better things to do, and of a greater variety of things to say when I do say them. Next year I hope to keep that up, while still having more things to offer here than tweet counts. Let's see how it goes.

I hope 2015 is very very lovely to you and that you thoroughly enjoy your time in it! See you later!

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.

"Will no one rid us of this turbulent membership?" Mustering techniques to avoid.

I am a member of CILIP. I am a member of CILIP because my peers at Library School were also members of CILIP. I am a member of CILIP because maybe at some point I’ll get around to doing the Chartership thing, even though I’m not still 100% convinced that the whole enterprise is not just a white elephant money-making racket for CILIP. I am a member of CILIP because I believe it is important for an area such as librarianship to have a strong body that represents the interests of libraries, librarians and library users.

Things I am not a member of CILIP for:

CILIP Update; training events (though some would be nice); opportunities to engage in democratic processes; small print scrutiny; trying to get my head around an at first glance bizarre organizational structure; reading a CILIP councillor describe dissatisfaction over a rebranding exercise as betrayal; reading an ambassadorial figure within CILIP shrugging off dwindling membership as aninevitable function of library closures and deprofessionalization; reading the same figure address the same issue of dwindling membership in terms of “quality over quantity” (I don’t want this to drift anywhere remotely close to an ‘answer-post’, let alone character assassination -- there’s something wrong-place-at-the-wrong-time about the CILIP President role and a slip of the keyboard is all too easy -- but just take a few seconds to mull the ramifications of that statement)...

There were more different types when I were a kid.

In case you’ve missed it, the fiddles are out at CILIP again, this time regarding the recent Governance Review. You may have missed that too. I’ve a distant hazy memory that I once followed a link from an email (though a search of my inbox suggests that I deleted whatever email provoked it), saw lots of boring governance stuff and then got distracted by BuzzFeed or something. It’s not really been a debate I’ve followed, partly because it’s not really been a debate. I could express in broad strokes (and reasonable reasoning) my sense of rights and wrongs, but the simple truth of the matter is that this one seems to have slipped under the radar in a way that the whole ILPUK debacle didn’t. It’s all building up to become another deck-chair shuffle of an AGM.

The first deckchair to be vacated was that of enfant terrible Tom Roper, who explained his reasoning in this blogpost. His resignation brought the detail of the Governance Review to the attention of twitter. In a way it’s a shame he didn’t go before the review’s close-date (think of the traffic!), but such is the nature of cause and effect. Council chair Martyn Wade responded to Roper’s post, and Roper’s replied here. And this weekend President Band added her thoughts. 

From my perspective, CILIP’s governance is overly elaborate: an elected President and Vice-President with so little power and influence that nobody stands anymore; an appointed Chair (to assume the Presidential office in the proposed regime) overseeing a mixed bag ofelected and appointed Councillors; a paid CEO “delivering the change programme and effective charity management”. Some simplification might not go amiss. The problem seems to be that the proposed simplification is a simplification that reduces rather than inspires member involvement: the role of President becomes more relevant but we’re not allowed to vote for who might get it anymore. The number of elected Councillors drops from nine (or potentially all) to eight, which seems another backwards step.

Voting in these things is always going to be pretty low. We don’t generally know enough about the candidates to feel too strongly. Therefore, CILIP elections should be at worst a formality and at best a way to engage any membership so inclined. So why reduce the membership’s say? That just seems stupid. Almost as stupid as ILPUK. Seriously... why would you do this?

Why?

And it’s the fact that CILIP’s powers-that-be seem to make such a mess of the easy things like sensible, positive member engagement that is so frustrating. These things should be non-issues.

So will this September's AGM be dominated by yet more belly-button stare-offs? Or will there be some motions tabled that actually engage with the very real threats that library services (in all sectors) are facing? Perhaps you have a motion you’d like to put forward. You’ll have to be quick, though. You’ve got until 5pm tomorrow (Monday 21^st July). More details on how to submit a motion can be found here. I hope some of you are more inspired than I am.

Barbara Band says some good stuff in one of her replies to a comment on her post:

“[We] are best served by having a national professional body that can have a positive strategic impact on Government policy... [via] the work it has done with the All-Party Parliamentary Group on Libraries, or the work on Information Literacy, or the policy responses it has submitted, or the frankly bloody good work that Annie Maguer does 7 days a week [weekends are important, Annie, but thanks all the same] on securing influence and credibility with policymakers... [This is] the only effective way to defend the long-term and precious principle of the right of access to library services... CILIP needs to influence Downing Street and Treasury... CILIP needs you... like we need everyone in the library and information professions.”

I agree with most of what’s being said here. This is the important stuff. Getting a clear message deep into the halls of Whitehall and Westminster. Saving libraries. 

Barbara continues: “We need a credible brand and a professional presentation.” This is at the heart of CILIP’s recent efforts regarding rebranding and governance. Maybe CILIP are right: politics has always had an obsession with image after all, especially in the last couple of decades. “Most of all,” Barbara adds, “we need people to understand that whether the battle is personal or professional, we get absolutely nowhere by fighting amongst ourselves.”

Which is the underlying sentiment of this post. It’s also why members get affronted when CILIP’s governance seems to start picking fights with them, be it by conducting lip-service surveys ahead of an unpopular namechange, or by reducing the few democratic pathways though which the membership can influence said governance. This is a particular affront at a time when some of the membership may feel that the governors in question are not doing enough on things other than name-changes and internal restructures.

It seems that I agree with Barbara (and probably the rest of the CILIP great-and-good) that we should stop fighting among ourselves and start concentrating on fighting for the future of libraries. What we probably disagree upon is the reason the in-fighting is there in the first place (and indeed the potential solutions to it). The disgruntled membership are not out to cause trouble for trouble’s sake and I doubt CILIP's leaders are too. We’re not the agents in this so it’s a bit galling when we’re told to be quiet.

The argument can perhaps be summed up as: 

CILIP MEMBERSHIP: Stop messing about!

CILIP GOVERNANCE: Stop arguing with us!

CILIP MEMBERSHIP: Stop making changes we don’t like!

CILIP GOVERNANCE: Shut up!

The elephant in the 'blog post.

I’ll be paying my subs for another year because I really really really want CILIP to be out there fighting for libraries. And I really want to be able to support them in that fight. But I’ll not be shutting up. And I will be exercising my ballot at the AGM. I voted for Tom Roper in the Council elections last year and I thank him for his efforts in representing the membership during his brief tenure. I also thank him for being a one-man publicity machine for CILIP’s internal democratic processes, as without his annual agitations the AGM and its agenda would doubtless pass me by un-noticed. Finally, I thank Barbara Band for everything she does in being an ambassador for The Cause. It’s far far more than I do and it’s very much appreciated. 

Full and frank debate is healthy and proper, and a necessary part of good governance (wherever possible taking place ex camera so we all know what’s going on). In the course of such debate we should, of course, never fail to lose sight of the fact that the real enemy is without.

Just a bit of fun...

Of all the PowerPoint slides I've ever made, I think this one may be my favourite:

It's really quite a simple one too: repeated freeform motion path animations with the smoothing turned off. Right click and save-as to download a version of it (1.7M).