Tuesday, 24 July 2012

Career Development Group National Conference, Birmingham, 2012


To say I was looking forward to this event is something of an understatement. As I mention in the first post of a new blog, this would be a big step for me. Indeed one might legitimately describe it as life-changing. This is becoming a bit of a habit for CDG conferences: it was their New Professionals Conference last year that broke my cynicism and inspired me to get involved with the wider library community. Without CDG there would be no Succentorship without Sneers

This year, the New Professionals Conference has been absorbed within the main event. I can see the point in this: I don't think it's healthy to ghettoise ourselves as graduates, and as the conference title puts it: "Together we are stronger". But some of the enthusiasm and energy I experienced last year seems to have been lost in the process of this merger, and I miss the focussed attention on graduate development. Still, it gets us newbies together with those who've racked up some practical library wisdom, and that is not to be sniffed at.

Liz Jolly's keynote address was a perfect example of this: an inspirational biographical account of her "learning journey" through the library profession and its supporting networks. She stressed the importance of professional involvement and warned us not to rely overly on the Twitter echo-chamber. Read journals and seek out different people; "not all heroes are on social networks". I was particularly taken by her advice to focus ones fire: to be disruptive but not irritating. Liz is proud to call herself a librarian ("if we're not proud, who is going to be?") and I appreciated the reminder that while we may often find ourselves complaining about CILIP, CILIP is its membership: we are CILIP. It inspired me to consider getting involved on some level or other, although I think I ought to wait until I get a job first.

Liz's address is exactly the sort of thing we graduates need: it was inspiring and informative. If I take nothing else from her talk, it is the value in life of a good haircut. That's advice I shall definitely try to follow!

The next session I attended was Steph Bradley's account of resurrecting a library service for the Bristol NHS. When she arrived it had no fixed staff, just a visiting librarian, and consequently the collection had suffered: items were out of date, stuff was missing, and the catalogue was riddled with inconsistencies.

Steph talked to colleagues and users (including getting out to remote users) accosting everyone with blanket emails both to establish need and to advertise improvements to the service. New books were bought, inter-library loans were enabled, and an improved website was created (including a useful "Where's Steph?" link).

Steph stressed the importance of being flexible and positive. We should shout about ourselves and our service whenever we can. Enable face-to-face contact, and remember that word of mouth is often a better advertiser than posters. Find out what our users want and prove our impact through feedback. Fit in within our organisation and seek to adapt others rather than letting them adapt us. Make use of our colleagues: find out what's worked before and what hasn't. Keep on keeping on: effort pays off in time.

I found Steph's experience uplifting and I hope I'm able to put what she's learnt to good use in my own career. By her own admission, she's been lucky enough to have good and helpful managers who've supported her in this project. I hope the same will be true for me.

After a brief break, it was the turn of Patricia Lacey & Emma Gibbs. Their presentation described the development of a library network to support knowledge sharing and facilitate job shadowing and mentoring. They organized whole day knowledge sharing events, and day-long job-shadow opportunities, getting para-professional staff involved (something which strikes me as a decidedly Good Thing). A similar arrangement was described in a later session by Kristine Chapman & Karen Pierce. They told us about CLIC: a cross-sector staff development group in Cardiff which organises job-shadowing, library tours and talks. It's a free service aimed at all library staff in Cardiff. It was funded by government grant until 2010, and since then it's had to rely on the good will of participants. Demand exceeds availability and the organization can no longer afford the larger venues it used to use for talks. The lack of funding led to the website stagnating somewhat, but it has recently been redeveloped as an information hub and social media takes a greater role in advertising events. Despite the difficulties with funding, the service is clearly valued, as a recent survey demonstrated, and the ability to network and learn about different sectors is highly regarded. I particularly liked the idea of the Do Something Different job-swap day. Clearly it's a shame that money is no longer available for larger scale projects, but it is great that CLIC has managed to keep going in spite of this set-back.

In the second of the day's parallel sessions, I chose Stephen Ayre's report on a collaborative research exercise within the NHS. It was a great insight into the health library sector. Stephen took us through the results of a recent survey of users who had received information skills training. Most such users were from nursing & midwifery and allied health areas, and the vast majority had used their skills since their training. The skills were mostly employed in research, advice for colleagues, and service development, with major influences of such application being treatment, advice, and guideline development. It was a useful demonstration of how collaboration between libraries can gather more meaningful results of service impact than might be achieved individually.

Time for lunch: a pleasant buffet, albeit with staff who were a little over-keen to take away our plates. The room was arranged around a number of podium tables that made mingling quite easy, and I enjoyed the opportunity to get to meet new people.

The afternoon began with a talk from Michael Martin about CILIP's Future Skills Project which has redrafted the "Body of Professional Knowledge" and is reviewing the processes of certification and chartership. Then it was time for a workshop session: I chose to attend Virginia Power's session on change. In addition to offering some useful advice regarding support networks, it was also another good chance to get to know other people at the conference, and it allowed me an opportunity to mention LISNPN.

For the final session of the day, I went to Stephen A Bowman's rousing appeal to outreach and advocacy. We should be getting out to schools and teaching them skills and aspiration; we should be volunteering for university committees to get ourselves and our libraries seen. Like Steph Bradley, Stephen acknowledges that he's had a certain amount of luck in having the backing of a supportive management for his endeavours. Still, if we evidence our projects properly, perhaps even the most unenlightened management can be made to give them some consideration.

Stephen won one of the two prizes for presentations, and the other was won by Rebecca Dorsett whose 'Shelving together' talk was the parallel session in the same slot. Although I didn't see it, I've since taken a look at the slides and I suggest you do too (and not just for the naked men!).

A conference is about getting to know people as much as it is about learning, and so while the presentations may be over, the day was yet young! There were two-for-one cocktails to be drunk in a bar in the city centre, and new friends to be made (although by the end of the night it was, as tradition dictates, the same familiar collection of University of Sheffield graduates propping up the bar!).

CDG 2012 may have lacked the focussed energy of last year's New Professionals event, and there was a sense that some of the content, while perhaps new to those longer in the career, was already familiar to recent students. I also worry that the mix of those attending was not as varied as it could've been: I welcome the opportunity to meet more experienced staff, but such individuals seemed well in the minority. I like the idea of a combined event, but I'm not sure it worked as well as it could have done. But perhaps this is just a case of first-year teething troubles. Despite these minor quibbles, the event was still a massively motivating experience, and on a personal level I feel tremendously proud to be part of what seems (not least on the basis of this conference) to be a genuinely supportive and inclusive profession.

Thanks to everyone involved in organizing the day, to the speakers for their motivating thoughts, and to everybody else who went for just being downright lovely. 

Sunday, 8 July 2012

Evelin's Evil Excel Exercises


My last post was perhaps a bit too much to take in in one go, so I've written a little walkthrough exercise to demonstrate how some of the stuff I mentioned might be put to practical use. The walkthrough is in 60 steps broken up into six stages of varying difficulty, and is peppered with screenshots. I hope it's understandable enough, and that it's of some help in getting to grips with spreadsheets. You can give it a go here.

Monday, 2 July 2012

How to Excel at Excel

Microsoft Excel is one of my favourite computer games. I find it endlessly playable. I even chose my dissertation topic with an eye on its potential for spreadsheet-based entertainment. But apparently not everyone feels the same way. Or perhaps they've just not had the opportunity to put in the same hours as I have, and never worked out how to get past that pesky end-of-level baddie in the Hall of Tortured Souls. So it was suggested to me on Twitter that I might cobble together a few Excel tips by way of a blog post.

I've been toying with Excel for about twelve years, from doing the accounts for a business I used to run to managing huge tournaments of a Formula One computer game, and most recently I racked up some stupidly big files while data-mining my way to a dissertation on catalogue search-term analysis. By and large, the skills I've picked up are more pertinent to data extraction than to simple accounting and record keeping (I am very much guilty of using Excel as a database, not least because I've always found it considerably more friendly than Access), but hopefully some of the stuff I'll mention here will be of some use. Some of it, on the other hand, will be baffling, and there will be the odd bit of egg-sucking to balance that out. I don't profess to know the full intricacies of Excel: I'm still very much learning myself. But over the course of the last twelve years, and particularly the last twelve months, here's some things which I've discovered and thought, if only I'd known that before:

EDIT: I appreciate that this list is a bit of a "wow, look at all the cool stuff" kind of mess, so I've also produced this little exercise that you can work through.

A fistful of Dollars

=$A$1+B1

Suppose we want to add the figure in column A to the figure in column B. We can type =A1+B1, and ta-da, there's our answer. And we can then drag that cell down and expand the formula for each row of the column: =A2+B2, =A3+B3 etc. All very lovely. But suppose we wanted to add all the figures in column B to a single number in column A (i.e. =A1+B1, =A1+B2, =A1+B3...), we can't just drag. We could copy and paste into each cell, and then edit the reference accordingly, but we don't have to. We can use a fixed cell-reference instead. We fix the reference by adding a $ to the front of whichever coordinate(s) we want to maintain. $A$1 is a constant reference to cell A1, $A1 will fix to column A but return the relative row, and A$1 will fix to row 1 but will return a relative column. 

A Colour Licence

Adding colour to a spreadsheet, be it cell colour or font colour, can be very helpful as a means of highlighting important data. It's also useful in so much as we can sort or filter by these colours. This can be particularly handy when used in conjunction with...

Conditional Formatting

On the Home menu, this is a useful tool for at-a-glance analysis and makes your spreadsheet very pretty in the process. As you add content and insert extra rows into your data, it's easy to get lost in overlapping conditionality, so it might be worth cleaning out any unused conditionality now and again using the Manage Rules dialogue.

Text to Columns

Ideal for imported data, but also handy for quickly splitting cells, the Text to Columns feature lives on the Data menu, and breaks up text into columns, either based on a fixed width or on a delimiting character of your choice.

Merging Cells

A pretty spreadsheet is a happy spreadsheet, and the Merge option on the Home menu is a button I use often enough to have it duplicated on the Quick Access Toolbar (something well worth customizing if you use Excel a lot).

Transposition and Other Pasty Plays

Perhaps we don't want our stuff arranged in columns. Perhaps we'd rather it were in rows. Well we can copy what we've got and then Paste Special, selecting Transpose in the dialogue. There's a load of other paste options in there too; particularly useful are Values (pastes the results rather than the formulae) and Format. We can also paste onto an existing array and perform a mathematical transformation in the process by using the Operation options.

Cleaning up the Content

=TRIM(A1)

=VALUE(A1)

TRIM will remove any unnecessary space characters in a text string so that there is only one space between each word ("  ducks   and      donkeys    " becomes "ducks and donkeys"). VALUE will ensure that anything that looks like a number is treated as a number rather than as text. Both are useful for cleaning up data before we start playing. 

Concatenation

=CONCATENATE(A1," ",B1)

=A1&" "&B1

Got some bits of data in different cells that you'd really like to be in the same cell? =CONCATENATE(A1," ",B1) will paste together the contents of A1 and B1, with a space character between (thanks to the " "). To save on columns (and typing), you can even concatenate on the fly within formulae by using &: =A1&B1; =A1/(B1&C1) (the latter example relies somewhat on B1 and C1 being numbers).

Substitution

=SUBSTITUTE(A1," ","")

SUBSTITUTE is our search and replace mechanism. If we want all the 10s in column A to become 15s, we can =SUBSTITUTE(A1,10,15) etc. More complicated search and replace exercises (including particularly useful format replacements) can be done under ctrl-H (it should be pointed out that Excel's Find and Replace dialogue has a curious blind-spot on the "~" character, as I discovered to my annoyance during my dissertation). I've also been known to make search/replace alterations in Word (Word's ability to replace punctuation is useful when preparing material before importing into Excel).  

Filter Tips

We can locate similar data using the Filter function on the Data menu. Filters are useful for comparing and working on related data within the larger set. We can filter by colour formatting and by various permutations of text content, as well as by cell values as a whole. 

A Friendly Sort

We can order our data alphabetically, numerically, or by colour using the Sort function on the Data menu. If you're worried about messing up the order of your spreadsheet, add a new column and fill it with ascending numbers (you can auto-fill an ascending sequence in several ways; here's two: 1) type 1 in the first cell, 2 in the second, highlight both, and drag down to auto-fill the sequence; 2) click the bottom cell of an empty array, press Shift and Page Up together repeatedly to highlight all the way to the top, and use the Fill Series option from the Home menu). Now you can sort by this column to return to the previous order.

Macro Economics

Macros are your friends. They may seem daunting but they will save you so much time. You can set up a macro in the Developer menu. Just click on Record Macro, give it a name and a keyboard shortcut, carry out some laborious, mundane task that you're fed up of repeating (like formatting a cell or setting up an elaborate sort routine) and then stop recording. Remember to select Use Relative References first if you want your macro to operate relative to the highlighted cell rather than in a fixed location. Two important notes to bear in mind: 1) when you save your spreadsheet, you'll have to save it as macro-enabled if you want to be able to use the macros again; 2) (and this is especially important) you can't undo changes made by a macro, so save your spreadsheet before running one. These inconveniences aside (and the latter point is quite an inconvenience), macros are great.

Graphic Design

Excel's good, but it has one horrible flaw: its graphs are a pain to format. I would that this were not the case, because it is capable of making some lovely graphs if one has time to spare formatting every single data series or typing out label-sets for scattergrams. To explain how to get the most of charts and chart formatting requires a blog post of its own. But we can plot some pretty things if we put our minds to it. We can scatter-plot coordinates to map locations, or use a surface chart to generate a relief, and afterwards we can feel smug about our achievements, happy in the knowledge that we have triumphed in the face of adversity.

A Domesday map of Strafforth plotted using Excel.

Doing your Sums

=SUM(A1:A10)

Simple but useful, this adds up all the cells from A1 through to A10. Beats typing A1+A2+... Current versions of Excel also give the sum of a selected array of cells (along with the count and arithmetic mean) in the display at the bottom right of the screen (very handy for quick reference purposes). 

What's in a Name?

The Name Manager on the Formulas menu allows us to give shorthand names to data arrays. If we find we're referring to A1:A10 a lot, we can go to the Name Manager and give this array the alias "a", allowing us to write =SUM(a) instead of =SUM(A1:A10). This is especially useful for more complicated arrays.

If...

=IF(A1=B1,"Oh, wow! A1=B1!","Meh...")

A straightforward conditional formula. We can nest up to eight IFs, i.e. =IF(A1<B1,B1,IF(A1=B1,B1)) which gives us the largest value of either A1 or B1. Many of the things we can achieve with IF can be done in a simpler way with other commands such as MAX and...

Choose Life

=CHOOSE(A1,"1st","2nd","3rd")

At first glance, CHOOSE seems to offer nothing that cannot be achieved by a nested series of IFs. But it's quicker to write out, and it has a maximum of 254 clauses to IF's eight. The above example assumes a value of 1, 2, or 3 in A1, and returns an ordinal equivalent. Any other value in A1 will cause a #VALUE! error. CHOOSE is reliant upon a numerical reference, starting from 1, so you'll likely need to create some sort of transformation on your data to get everything set up for it.

Untangling Knots

It's easy to get lost in formulae. Fortunately, the Formulas menu has some useful tools including Show Formulas which does what it sounds like it might, and Trace Precedents / Trace Dependents which draw lots of coloured arrows all over the place to show the trail of your data that you might more easily unpick your knitting.


Coming to terms with ones Errors


=IFERROR(A1,0)

=ERROR.TYPE(A1)

Fed up of pesky errors such as #VALUE! or #DIV/0! messing up your spreadsheet? Stick in an IFERROR and it will clean any error away and replace it with whatever you prefer (in the above case, "0"). If you want to differentiate between different error types, you can use ERROR.TYPE to return the numerical value for the error: #NULL!=1, #DIV/0!=2, #VALUE!=3, #REF!=4, #NAME?=5, #NUM!=6, #N/A=7 and #GETTING_DATA=8. 

Big Ones, Small Ones, Some as Big as Your Head

=LARGE(A1:A10,1)
=MAX(A1:A10)

=SMALL(A1:A10,1)
=MIN(A1:A10)

=RANK(B1,A1:A10,0)

In the above examples, the paired formulae produce the same result: =LARGE(A1:A10,1) and =MAX(A1:A10) both find the biggest number in the array (A1:A10), for instance. But LARGE and SMALL can also find the nth largest or smallest number. If we wanted the third largest number in an array we'd use =LARGE(A1:A10,3)

RANK is a related function. In the example given, we are finding the numerical position of the value B1 among the values in the array A1:A10. The "0" indicates that we are ranking in descending order ("1" would be ascending). So if A1:A10 were the numbers 1 to 10, and B1 was 1, this formula will give us a value of "1" (i.e. "1st"), and the same result would occur if the values of A1:A10 were 10 to 1 or any jumble of those numbers.

Measuring Length


=LEN(A1)

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1

LEN is a simple little function: on its own it will give us the number of characters in a cell. There is no equivalent tool for measuring the number of words, but we can use the longer formula above, employing TRIM (to get rid of any extraneous spaces) and SUBSTITUTE (removing all the spaces) to effectively do a space-character count, and hence (by adding the 1) the cell's wordcount.

Learning to Count

=COUNT(A1:A20)

=COUNTA(A1:A20)

=COUNTBLANK(A1:A20)

=COUNTIF(A1:A20,"hello")

=COUNTIFS(A1:A20,"hello",B1:B20,"world")

Here are a handful of ways of counting cells. COUNT counts cells that contain any number, COUNTA counts cells that contain anything at all, COUNTBLANK counts cells that contain nothing at all, and COUNTIF counts cells that match a defined value ("hello" in the example above). There's also COUNTIFS, which will count entries fulfilling multiple criteria: in this case, "hello" and "world" in adjacent cells of a two-column range.

String Theory

=LEFT(A1,5)
=MID(A1,1,5)

=RIGHT(A1,5)
=MID(A1,LEN(A1)-4,5)

The above pairs of formulae return the same content as each other: the first pair will give the first five characters in a cell, the second pair will give the last five. This is useful for data extraction, but we can do more with MID if we feel compelled. We can, for instance, further cut down on any IF / CHOICE programming by creating a reference string. For example, we could convert a number in cell A1 to a letter using a reference string such as "abcdefghijklmnopqrstuvwxyz" in B1: =MID($B$1,A1,1). We can reverse this process using:

Advanced String Theory

=FIND(A1,$B$1)

Keeping the "abcdefghijklmnopqrstuvwxyz" string at B1, but with letters in A1, this formula returns the numeric position, so "a" (and indeed "abc") = 1, "z" = 26 etc. FIND is case sensitive, so we might also choose to make use of...

Case Sensitive

=UPPER(A1)

=LOWER(A1)

These functions standardize the case of a text. There's also PROPER Which Capitalizes Everything. On a related note:

Code Selfish

=CODE(A1)

=CHAR(A1)

CODE operates using an internal text string: the Unicode character reference. It takes the first character in a given cell and converts it to the decimal version of the Unicode reference for that character, so "0"=48, "A"=65, "a"=97. The process can be reverse using the CHAR function. If we start messing around with Unicode references, we might also find the following useful:

Hex Enduction Hour

=DEC2HEX(A1)

=HEX2DEC(A1)

These functions convert a decimal number to a hexadecimal one and vice-versa. There's also some binary functions. It saves having to do complicated base conversions using maths. Speaking of different bases:

The Trouble with Time

=TIME(A1,B1,C1)

=HOUR(A1)

=MINUTE(A1)

=SECOND(A1)

=QUOTIENT(A1,1)

=(MOD(A1,1))*100

Time, with its pesky division into 60s, is a troublesome beast, especially if we're processing imported data. Excel treats time as a part of a larger date, and as a fixed point rather than a duration. Both these details can prove problematic. The best approach I've found is to keep separate any hours, minutes and seconds in different columns, and then combine them using the TIME function above (I then give it a pretty custom cell format: "hh:mm'ss""). The process can be reversed using the HOUR, MINUTE and SECOND functions. If a time has been entered as a decimal (e.g. 1.3 rather than 01:30) we can separate the numerator and denominator using the QUOTIENT and MOD functions (multiplying the latter by 100 to get "30" rather than "0.3"). This latter trick applies for any decimal expression, of course. We can also play about with these various functions to create decimalised times if required.

Blinda Data

=DATEVALUE(A1)

This time function is particularly handy for extracting Excel-friendly date values from imported text. Suppose cell A1 contains "21st May: Went swimming", we can do something like: =DATEVALUE(LEFT(SUBSTITUTE(A1,"st",""),6)) to shave off the "st" and work our magic on "21 May". We can then format the result as a date in the cell format options. DATEVALUE ignores blank space, so if we're doing a batch of diary entries of the same format we can safely replace the colons with a lot of spaces and up the character limit for extraction so as to include dates of a different character length.

Now Now

=NOW()

Today's date and time. Handy for date-maths. 

Then Then

=ROMAN(40)

A spreadsheet based pun there for you all. x