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.