Does David Cameron have to take off his shoes when he passes through a scanner? I don’t know; but the answer to that question, you’ll agree, rather depends on whether the Prime Minister has to pass through a scanner at all.
They don’t take my calls at 10 Downing anymore, so I am left to speculate. But his international itinerary is a bit more confirmable, having in fact been committed to the public record, at least for his trips through March 2014 by the Guardian’s Data Blog in tabular form, its current iteration (that’s to be explained) duly copied-and-pasted and slightly reorganized by yours truly into this worksheet:
David Cameron’s international trips
(And if you can get that right-clicked Export to Microsoft Excel menu command to work here on the Guardian site – or anywhere else- identify yourself and report here immediately to claim your reward.)
Three columns and but 116 rows worth of official trips should prove eminently workable, but let’s see. As intimated above, the data underwent a change or two from after their coming out some days ago. The companion Guardian story reports 47 countries among the Prime Minister’s line-of-duty destinations, but if you had run this pivot table on the original data (though if you hadn’t see those you’ll have to trust me here):
Row Labels: Country
the table would have draped a directory of 50 countries down the Row Label column. The reason for the discrepancy: the duplicative spellings United States/USA and United Arab Emirates/UAE. Excel doesn’t know any better, or course, and as such treated those four usages as four countries, an impermissibly misleading redundancy. In the interests of deep background, I brought these excesses to the attention of article author Ami Sedghi via a Tweet, and the repairs were made (I never received a reply from Ms. Sedghi for my citizen-journalistic contributions, though we could wonder if the Guardian had made the discoveries independently).
But even with all that vetting I still get 48 countries, and haven’t found any third instance of a same-country-entry inconsistency – not the biggest deal to be sure, but 48 isn’t 47.
In any event, now that the one country/one name ratio has been restored, a country count follows pretty straightforwardly:
Row Labels: Country
Values: Country (Count, and sort Largest to Smallest)
I get, in excerpt:
As the Guardian explains, the Belgian predominance has everything to do with European Union-seat Brussels, and Cameron’s recurring need to deal with and justify his coolness toward that organization to its other members.
But the real fun happens over in the Date of Trip field, particularly if you’re keen to calculate trip durations. First note the this-way-and-that alignments of its entries, e.g.
The scattershot left-rightness of the data isn’t borne of some random indecision; rather, it attests a pair of data types asserting themselves in the field’s cells. 20-21 May 2010 is clearly a label, for example, and in its present balky state thus resistant to any numeric services we might ask it to perform. The right-aligned 11-Mar-11, on the other hand, is just as clearly a date/number; click its cell and observe the Custom (as in custom date format) legend registered in the Number format field in the Home > Number button group for verification .
Be that as it may, if I do want to get to those trip durations (even without thinking about whether to regard the very first pair of trips, the ones to Berlin and Paris and their identical dates, as one or two visits), I’d first want to insert three columns somewhere, these to be respectively titled Start Date, Finish Date, and Trip Duration, or something like that, the first two formatted in date terms, the third in numeric, no-decimal mode. I’ve chosen columns D through F for those remits.
But we’re not having fun yet, are we? Ok; so let’s start with the actual date-formatted trip data (understanding as a first, or last, principle that any encompassing, formulaic workaround must be ultimately be IF-driven, so that all data-type contingencies could be enabled to yield a trip duration). By apparent definition these dates comprise one-day journeys – because a longer trip would have expressed themselves in label terms, e.g. 20-21 May 2010.
Thus for starters, and for example’s sake, I’d click in D29 – because A29 stores the 11-Mar-11 date, and because I assigned the Start Date field to the D column – and enter
=IF(ISNUMBER($A29),$A29,””)
Here we’re testing the entry in A29 for its numerical pretensions. If a number it is – that is, an authentic number formatted in date terms – the formula simply returns that date/number. If A29 is a non-number, then “” moves into the cell – although we need to understand that the double-quotes are nothing but a holding action, mounted in anticipation of a more substantive riposte to any label that might have laid its claim to A29 or any other cell in the data set.
I’d then copy precisely the above formula to E29, wherein the End Dates have set up shop. Why? Because again, any truly date-formatted entry stands for a one-day trip, and so Start and End Dates must of necessity converge. And in F29 – next door, in the Trip Duration field – I’d offer
=E29-D29+1
a formula that simply subtracts the End Date from the Start Date, adding a 1 because we want a trip beginning and concluding on the same day to evaluate to one day, and not no days. The formula’s universal, sure – but only if and once the other trip entries – right now inactive labels – could somehow be made to free their inner, usable numeric status. And that’s what I’m working on. (Hint: I think the Start Date is a lot harder to nail down than the End Date.)
And unlike the Prime Minister, I’m working in economy class; and the guy in front of me is leaning back into my coffee, my laptop has 12 minutes worth of charge left, and we’re still only halfway over the Atlantic. And I still can’t figure out to play that bowling game onscreen.
spreadsheetjournalism 
Leave a Reply