Our poke through the stacks of the London borough of Barnet’s libraries and its data necessitates this bit of whispered repetition: If you’re duly pleased with its compendious workbook as it stands – with its week-driven worksheeting of the libraries’ visits and its attending monthly and yearly summations – then you can stop poking, and feel free to yank your favorite pot-boiler from the fiction shelves and have a seat (though you’ll have to use my card if you actually want to borrow the book).
But again, if you want to do something else with the data – something that isn’t already there and thus requires your expert intercession in order to bring it to light – then I repeat: all the visit data could have, and probably should have, been reined into a single data set, from which all the multitudinous sheets could have sprung, along with those something elses.
But you heard all of this last week, and anyway it’s hard to issue pronouncements from a soap box when you’re whispering, so let me step down, speak up, and move on.
First, for those of you worldwide who’ve been wondering – and I put this question to an actual Barnet librarian – the daily-visit numbers beam their way into the data from an electrified turnstile through which patrons pass, after which the number crunchers divide the total by two. And the MOB library “branches” cited in row 19 across the worksheets signify mobile, book-bearing units that regularly set up temporary shop at selected Barnet venues (thanks to my wife for that datum), while the Home entries in row 20 point to home delivery services available to residents unable to travel to dedicate branches.
Second, and before we get a little bit fancy, you’ll want to take note of a few oddities that seem to have worked their way into some Total visits calculations (in the P columns) in the monthly aggregate sheets. Note the July sheet arrives at its totals by subjecting the daily figures to a SUM function, but June opts for understatement, e.g.
And June is right. SUM adds (no pun intended) nothing to these kinds of results, and should be earmarked for ranges of contiguous values only, e.g.
But because the total visit numbers are segregated in alternate columns, each contributing cell need be typed or clicked -the kind of work to which SUM can’t properly address itself.
But a set of more substantive and disturbing calculations have stolen into the Aug Total visit field, e.g. in P5:
Apart from the dispensable SUM, two references – the ones that should be counting Monday and Friday visits – are missing for each library down the column, and I don’t know why. Reasons aside, the pertinent D and L-column figures need to be restored to the formulas, or their answers will remain unambiguously wrong.
Now given those every-other-column sums for the Total visits and Total hours open fields which require for their summing a whole lot of irksome clicks, I began to think about more graceful alternatives to those formulations as they stand – and it seemed to me that some array formulas might perform those addition tasks gracefully indeed.
You remember array formulas – those super-charged expressions that do the work of many formulas in but a single cell’s worth of syntax. We said something about them there and I’ll spare you the review here, but my first array, composed in a new column and seeking to add only a given branch’s hours, looks like this, assuming we direct our attentions to the Burnt Oak branch in row 5 (I’m looking at the August tab):
So what’s this array formula doing? Put programmatically, it’s stipulating that, if any given column heading contains the word Hrs., then sum only those values in row 5 than find themselves beneath one of those headings. And what about the VALUE? It turns out that the Hour data in the monthly aggregate sheets possess textual status (about which there’s a bit more to say), and so the VALUE corrective makes these ersatz numbers real. All this again is being conjured in one formula, and once properly assembled it can be copied down the column. And be advised that array formulas (at least the kinds we’re offering up here) must be installed into their cells by a simultaneous tapping of CTRL, SHIFT, and ENTER (for another useful introductory look at array formulas, make your way here) .
What’s cool about this particular manipulation is that it scans columns for the textual handle it needs, in contradistinction to typical array operations (e.g. COUNTIF, which I’d allow is an array function) that search row data.
But now if you want to gather a seven-day visit total for any branch, in view of the like complication that each day is interpolated among the Hrs. fields and thus separated from its companions, we’re also faced with a small and new predicament – namely, that each visit field-to-be-added is uniquely identified by a day of the week, and hence can’t be addressed by a single search term such as Hrs. But we could write this:
Now that looks pretty elegant. All we’re doing here is standing the Hrs. item on its head, as it were. We’re looking for all the field headers in the identified range that don’t comprise the term Hrs., and that Boolean back-door tack seems to work.
Now about the matter of the HYPERLINK(S), and the function bearing that name. As one might rightfully suppose, HYPERLINK stations an address or location (e.g. a web site link) between its parentheses, enabling the user to click its cell and travel to the location linked (for an overview of HYPERLINK and its syntactical requirements look here, and look closely at the examples). You’ve probably noted in turn that most of the monthly aggregate data has submitted themselves to hyperlink form; but it seems to me that they’ve gotten it wrong.
One assumes first of all that the hyperlinks here mean to enable the user to commute to the cell location(s) referenced in their expressions. But HYPERLINK formulas appear only to promote the movement to a singular location when clicked (see this discussion thread, for instance) – and the Barnet hyperlinks group multiple cell addresses, and I don’t think you can do that. Indeed, clicking any such cell uncorks an error message, or at least it does for me.
Note in addition, however, that the HYPERLINKS seem to be able to add the values denoted by their several addresses – even though if you visit any of the cells in question (to click and remain in the cell without firing the hyperlink you can, among other devices, click to the side of the value posted to the cell) and click the Number format drop-down menu in the Home tab, you’ll be told that the cell is in effect text-formatted. We thus seem to have met up with one of those data-type admixtures that seem, depending on the operation to which they’re subjected, to yield textual or numeric outcomes. Click, for example, on a blank cell in the July monthly tab and enter
and the Wednesday visit figure of 863 for the Burnt Oak branch will indeed multiply to 2589. But add the H column via a by-the-book SUM operation and you’ll get…0. Here, then, the HYPERLINK math doesn’t compute at all, the data falling back to text, and I don’t quite understand it all. But observe the ungainly, cell-by-cell summing of the H data in H21. That works, and again I’m not sure why.
But if you want to smartly add that H column, why not try:
That pretty simple array formula seems to work too. But stop me before I start feeling proud of myself.