First a clarification.
This blog is about the many questions asked on Excel forums, where the question asked is ‘how can I do this using this tool or feature’. The answers, therefore, are necessarily confined to how to do it using that tool or feature. Sometimes, or often in my experience, the answers miss the obvious. That’s not the fault of the great people answering the questions, but a fault of the boundaries of the question. The scope excludes the obvious.
A further observation is, and I write about this a lot (!), the question asked often begs another question; a wider question. Such as, ‘why are we doing it this way’? Or, how does this problem arise in the first place? And … er … but what happens next?
Crossing Abbey Road
I’m sure we can think of three main ways.
London Underground
The Beatles could have taken bus No 139 going North on Abbey Road to West Hampstead station, changing to bus No C11, to take them to Finchley Road underground station. Then take the Jubilee Line Southwards to St Johns Wood, and walk about 5 minutes along Grove End Road to Abbey Road. They’ll have crossed the road in about 45 minutes, at a cost (today) of around £8 each.
A GPS app, such as Google Maps will have suggested this as the ideal route if you specify that you want to take public transport only.
This strategy works because Finchley Road station is on the West side of Finchley Road, and St Johns Wood station is on the East of Abbey Road. Thus, the tube train will effectively assist the Beatles in crossing Abbey Road. (Note that Swiss Cottage, which is closer, is unsuitable because it is also on the East side on Finchley Road and a road will need to be crossed, therefore. This may be unacceptable to some purists.)
Helicopter
By 1969 the Beatles were individually very rich. Also, they brought in a lot of profit for EMI, the recording company. So, couldn’t EMI hire a helicopter for the Beatles to cross Abbey Road?
If the question(er) limited the answers to this option, there is a further choice. How far is a suitable take off point to the West of Abbey Road, and a landing site to the East? Or, if that’s not practical, why not winch the Beatles on board? In that case, should we winch them individually, or together while singing ‘we all live in a yellow helicopter’?
The cost I reckon, in today’s money, will be over £2,000 and the entire operation may take a whole morning.
Just Walk Across!
This is the least techie option. Not at all sexy. If you’ve been to this location you will see that the crossing is just opposite the exit from the EMI Studios on Abbey Road (since renamed Abbey Road Studios).
Standing on the pavement, by the crossing, you will notice that the other side is approximately 50 metres away. The crossing is clearly marked. It’s obvious from the six black and white stripes boldly painted on the road.
We know a few more things. It is not necessary to step exclusively on the white stripes and jump over the black ones, or vice versa. It is the traffic law that traffic should stop when someone is crossing. Although, in the past 50 years regular motorists on this stretch of the road drive on out of protest or frustration (!) – with or without an audible swearing aimed at the fans/prilgrims who have come from all over the world just to get run over or sworn at by the London black cabbies.
Almost intuitively you will realise that walking normally, putting one foot ahead of the other, will get you across in approximately twenty steps. If you study the cover of the Abbey Road LP you can guess that the Beatles needed about that many steps. It’s not important which foot leads. (You will notice that McCartney is out of step with the others and barefoot. This is to indicate that, although he is in the picture, he is actually apparently dead. And yes, he would have been ’28 IF’ he was not)
The cost? Nil. And the time to cross is about 15 seconds. (You will see in the YouTube video on the 40th anniversary of this iconic photograph 8/8/2009 that I did this quicker but I was making a dash for it. See below).
By simply walking across, the Beatles made a better album cover. I can’t imagine one where they’re are on a bus and a tube train. Or being winched on board a helicopter.
Excel
As I said, there are always more difficult routes. If the obvious is too boring, the more exotic routes are more attractive.
This stream of thoughts was inspired by Bill Jelen’s question No 2316 he posted this week. Thanks, Bill.
Apparently the questioner asked for a solution with PowerQuery. Bill reckoned his answer is not the best, so he was asking for better ones. PowerQuery Grandmaster, Oz du Soleil comes up with this. The context is clear; what’s required is a PowerQuery solution.
In the Beatles illustration, that’s similar to asking ‘what’s the best way to cross Abbey Road using a helicopter’. Potentially, we can have an entire conference on this. Or indeed, one on the best route by public transport. Or ocean liner. Or spacecraft.
My Solution
I like to study the thinking process; of how a choice is made on ‘how’. Which involves asking ‘why’. for example, why is it necessary to use a helicopter? (eg. PowerQuery, XLOOKUP, Dynamic Arrays, [insert latest gizmo])
So, I’m asking – what is the thinking process for stepping on to the pavement on the West side of the road? What and where is the destination? What appears to be the steps required? (Pun intended!)
You can see my solution explained here [TBA]. But, essentially, we’re asked to convert a set of data that’s laid out in a (rather common) crosstab layout into another (rather common) crosstab layout . The only difference is that a ‘grouping’ (Employee) is removed and made into a field in the resulting table.
[IMAGE]As with the zebra crossing right in front of us, we should be able to see the path to the destination quite clearly.
Then just cross by walking …
… just as the Beatles did on 8 August 1969.
Of course, one could argue that crossing the road by walking has some inherent dangers. (On the day, traffic was stopped for a few minutes while photographer Ian McMillan took six shots standing on a ladder)
Related Information
It seems I am the only Excel Consultant who has also started a spontaneous celebration of Beatle songs on Abbey Road with 3,000 fans from around the world. This happened on 8 August 2009.
24 million views can’t be wrong! This sketch really sums it up. How expert do you need to be to draw seven parallel red lines horizontally?
Add comment