Accessibility gone mad?
Doorbell for the disabled? Or one that no longer works?
Sign o’ the times
I stumbled upon this shop in King’s Cross yesterday.
It struck me as a little odd.
01 phone numbers were replaced with 071/081 in May 1990. which were in turn replaced with 0171/0181 in April 1995, and with 020 in April 2000. (As an aside, to research the dates I searched google.com for "01 071 0171 phone numbers", to find my own post of May 2006 topping the rankings. A cause for concern? Possibly.)
You’d have thought a sign shop would be well placed to replace its own signage more regularly than every 17 years.
Call yourself a dev.?
I have a colleague. For the sake of argument, let’s call him Neil. (His real name is Neil.) He sent me an email today asking why the following Excel formula wasn’t working.
=IF(F648=1, 10, IF(F648=2, 8, IF(F648=3, 7, IF(F648=4, 6, IF(F648=5, 5, IF(F648=6, 4, IF(F648=7, 3, IF(F648=8, 2, IF(F648=9, 1, 0)))))))))
[Note: I've added spaces after each comma to allow the for inevitable word-wrapping issues.]
My immediate response was that Excel can only cope with eight nested ifs. (The last argument in his email was red, and this was indeed the straw that was breaking the proverbial camel’s back.)
I then asked what business problem he was trying to solve. He had a column of data containing values between 0 and 10. And he wanted to invert them, so that 0 became 10, 1 became 9, 5 stayed 5, 8 became 2 etc.
I suggested he instead used the following formula:
=10-F648.
There was a short pause on the other end of the line. Bless.
Red Sox comebacks
It seems the Sox have done it again: from a seemingly hopeless
situation in the championship series, they’ve gone on to clinch a berth
in the World Series. In 2004, they were 3-0 down to the Yankees
before rattling off the next four games. The seventh game of this
series was played the night we landed in New York furnished with
visas: 21 October. I went down to Penang on Columbus to order
takeaway beef rendang and pancakes, where I caught the start of the
game and was filled in on the first six games by the barman over a
beer. Then back home to catch the rest of the game, managing to fend
off the jetlagged eyelids that seemed intent on closing. Fabulous to
see the Yankees lose in such dramatic style.
This year, the Red Sox have turned around a 3-1 deficit to the
Cleveland Indians, winning the final game in style 11–2 and scoring six runs at the bottom of the eighth, to set up a World Series against Colorado (one of only two rectangular states, the other being Wyoming). This must all be particularly hard on Johnny Damon, who defected from the Red Sox to the Yankees after their 2004 glory. Poor Johnny.
I wouldn’t ever want to face the Sox in a championship series. Not
likely that I will, but still.
Lovely ad.
Kicker bokke glory
I thank you.
[Dan bows, then send his CV to the Sun in response to their advert for Chief Headline Writer.]
Barriers to entry
I tried to download a programme for the BBC’s iPlayer tonight. Below were the hurdles I had to jump over to get to where I wanted:
- You’re not using Internet Explorer. That’s the only browser we support. [Dan switches to IE]
- You need to install iPlayer. [Dan installs iPlayer]
- You need to register for iPlayer [Dan registers]
- You need to install a security update for Windows Media Player. [Dan downloads]
- A license [sic] is required to play the selected video content. Are you sure you want to open the web page to obtain the license [sic]? [Dan hits Yes]
Not the best user experience to get someone to use your product. I then had to download the 578Mb programme itself (titled Beautiful Young Minds). I’m 7% in so far, at which point I realised it was sucking the life out of any meagre bandwidth I might have wanted to use to, say, post about the ordeal I’d just gone through to register.
It’s hardly Joost now, is it? The content may be better, but the UE sucks.
Update: iPlayer uses a P2P thingamyjig called Kontiki which hoards your bandwidth and gives your CPU an unnecessary workout (courtesy of a process called Kservice.exe). And this is when iPlayer’s completely shut down. Dreadful, BBC.
Space: the final frontier
I hate incorrectly formatted space and punctuation in documents, even though it rarely has any bearing whatsoever on the aesthetics of the printed copy.
An italicised word or phrase should not have its surrounding spaces or succeeding punctuation mark italicised, for example. Underlining trailing spaces is common on the web, and drives me nuts.
I also hate redundant space in documents, something that baffles anyone I tell. A couple of trailing spaces after a full-stop, after which a new sentence once started, have no place. Please get rid of them for me.
See you next Tuesday
I’ve often struggled with the ambiguity of phrases like this week and next week. And any reference to specific, proximous days, like this and next Wednesday.
Let’s start with this week. Assuming a Monday–Sunday week, my strict interpretation of the phrase is that it always means the week that you’re in. So if you say it on a Monday, it means any time until the following Sunday; if you say it on Saturday, then it means today or tomorrow, assuming a future event.
Next week means any time the following week. So if said on a Monday, it means between seven and 13 days out from today. If said on a Sunday, it means any time from tomorrow until a week today.
It appears that this is not everyone’s interpretation. If said sufficiently close to the end of the week, Friday say, this week apparently means any time from the imminent Monday until a week on Sunday. I’m not sure what next week means in such circumstances; one can only assume it means the following week, given the relationship between this and next.
Which brings us on to next Wednesday. Does this mean the next Wednesday with which we are blessed, or Wednesday of next week? If said on a Tuesday, common usage suggests that we’re talking eight days from now. But if said on a Thursday, I think I’d assume six days from now. Leading me to believe it means "Wednesday of next week". But on Sunday, someone referencing next Monday would probably also mean eight days from now, which is neither the next Monday nor Monday of next week.
Can someone give me the unequivocal truth, please? Surely it can’t be open to interpretation.
Argentinian beef
A little harsh of the Wikipedia contributor to consign Argentina to defeat with 19 minutes left on the clock, trailing 24–13.
Likely, but harsh nonetheless.
Le rugby
Bonjour, ça va?
Non.
Friends Reunited: now that’s personalisation!
Every so often, I get an email from those nice people at Friends Reunited, a site I’ve barely returned to since registering during the buzz that surrounded its launch. Here’s the title of the email:
Dan, New people listed on Friends Reunited
And here’s the crux of the email itself:
Your Friends Reunited Update: NEW people listed and NEW profiles for you to read.
Putting aside the grammatical faux pas of succeeding a comma with a capital letter in the title (something that never fails to irk me), it’s not particularly compelling is it? Two or more people who I may or may not know have added themselves to the FR user base. And there are two or more new (NEW, even) profiles for me to read too, which I suppose could actually be those of the two (or more) people who’ve just registered. Forgive me please for dragging my mouse swiftly towards the Archive button.
Dynamic named ranges—poetry in motion
I’ve recently discovered the huge power offered by combining the offset function with named ranges in Excel. Allow me to share. No, please.
Offset is quite a new one to me, but it’s phenomenally powerful. It allows you to refer to a range of a chosen height and width that is a chosen number of rows and columns away from another range. Its syntax (or one of two possible syntaxes) is:
=offset(range, rows, columns, height, width)
Range is the original range, which only actually needs to be a single-celled range (an amoebic range?) Rows and columns are the respective offsets, while height and width define the height and width of the new range.
So offset(A1,2,3,4,5) will give you a range 4 rows high, 5 rows across that starts 2 rows below and 3 columns to the right of A1 (i.e. D3:H6).
Named ranges are also great. You can reference them in formulae, and if they change in dimensions, all you need to do is change the reference behind the named range, and all of your formulae update accordingly. Fantastic!
But what if you know in advance that your referenced range is going to change? And what if the height and width of the range can be determined based on the contents of a column and row?
So let’s combine the two. Let’s assume you have a range of data that starts in A2, with column titles in row 1. And let’s assume that your columns and rows are contiguous (i.e. no blank rows or columns), and a column title signifies that it should be part of the range, while an entry in column A qualifies that row as being part of the range.
Create a named range (Insert | Name | Define) called my_data. And name the range as follows:
=offset(A2,0,0,counta(A:A)-1,counta(1:1))
(Note, the zeros mean that we’re not actually using the offset bit of the function, but that’s by the by.)
The range referenced will start in A2, will (if the earlier assumptions hold) end at the bottom-most row which has a value in column A and go as far right as there are columns with a title. The "-1" adjustor is to account for the title in column A, which you don’t want to count.
By having a dynamic range, you can add rows and columns to a range that is being looked up in without fear of rows or columns being missed out of the look-up. So a database of contacts can be added to and the new names will be included in any referenced range; and new columns of data can be added with similar confidence.
Combine this with the suggestion in an earlier post to parameterise your lookup offsets, and you’re in vlookup heaven.
Antipodean accents
Not sure why, but the antipodean accent is resonating particularly sweetly this morning. This morning, two girls and a guy on the bus, all Kiwis, discussed their weekend’s beer-drinking exploits, and the "empty" feeling they felt after Saturday night’s game.
It’s a shame I don’t have any meetings with any Australians or Kiwis today. Maybe I’ll arrange a couple.
Dynamic vlookup offsets
vlookup is a hugely useful formula in Excel. I’d say that apart from the basic mathematical operators and the if statement, there’s nothing that touches it in terms of how often I delve for it in trying to solve a problem.
But it’s a very unstable formula. Here’s its syntax.
vlookup(needle,haystack,column number to return,exact match?)
The issue comes when the range that you’re looking up in is liable to change in structure and size.
The needle is unlikely to ever change, so that’s not a problem. Meanwhile, the haystack is likely to be a fixed range, and if it’s not, it can be dealt with by using a names range. The column number to return is the weak link.
In the past, I’ve always hard-coded this, not thinking to do things any differently. But recently, I found a way of making it dynamic.
At the head of the column in which you’re looking up, likely in a hidden row, store a value that represents the column number that the column represents in the range. So if your data runs from row 3 to row 100, with column titles in row 2, and the column in which you’re looking up is column A, put the following formula in, say, D1:
=column(D3)-column($A$3)+1
In this example, it will return the value 3.
Copy this formula across the relevant section of row 1, and the row can then be hidden.
And in the lookup itself, if you want to bring back column D, use the following formula:
=vlookup(XXX,$A$3:$Z$100,D1,0)
for an exact match.
By doing this, if a column is inserted somewhere between columns A and D, the value of D1 will automatically increase to 4, and your vlookup won’t break.
Hope it helps.
Feynman wiseman
"Looking back at the worst times, it always seems that they were times in which people who believed with absolute faith and absolute dogmatism in something. And they were so serious in this matter that they insisted that the rest of the world agree with them. And then they would do things that were so directly inconsistent with their own beliefs in order to maintain that what was said was true."
Feynman, R. P. (April, 1963), The Meaning of it All. London: The Penguin Group
Windows Mobile’s Christmas reminder
A couple of days ago, a little isosceles triangle appeared in the status bar at the top of my phone’s Windows Mobile interface. It has a point at the top, at which its highly inclined equal sides start, with a shorter, horizontal side finishing the triangle off at the bottom. The icon is shaded the same colour as its sides, so it’s solid.
I can only imagine it signifies a Christmas Tree, some form of reminder that Christmas is only 80 days away.
Which came first: the diversion or the road closure?
The sign at the top of St. Margaret Street just outside Parliament reads "Road closed following diversion", with an accompanying arrow directing traffic towards Victoria Street. Surely it should read "Diversion following road closure". Unless, of course, the traffic police decided to put in place a diversion for shits and giggles, after which the road was closed due to lack of traffic.