Calendar spreadsheet: explained
I tidied up the calendar spreadsheet last night while awaiting for our Ocado delivery. Plenty of time available, given they arrived over an hour later than their billed time. A tiny bit of the logic was tidied up, and I added a year-view. Overall, it’s now the dog’s bollocks, if I may say so.
Below is the logic I used, in English rather than Excel.
The first thing to do was to confirm the weekday of the first day of the year. So I took the value from concatenated the year on to "01/01/", and calculated its weekday from there.
Next, I created a table showing each of the month-lengths, eleven of them hardcoded, February’s using the slightly cumbersome formula for working out whether a year leaps: divisible by 400 is; divisible by 100 is not; divisible by 4 is; otherwise, not. The table also contains the weekday of the first of each month, calculated as the mod base seven of the previous month’s start date plus that month’s length less one.
On the calendar sheet itself, the first row of January’s dates was created by comparing the number of the day of the week each cell represented with the day the month started on to establish whether to show a blank (the month hasn’t yet started) or a number, either a 1 for the first day, or the previous cell plus 1 for subsequent days.
The rest of the rows’ entries simply compare the previous entry with the number of days in that month. If the two are equal, then that and subsequent entries display blanks; if not, then it’s time to increment the previous entry by one.
Each month has six rows of entries to accommodate the rare months that start late in the week and drip a day or two into week six.
And the cells’ formats are dependent on their contents and the weekday. Blank cells have no borders; those representing weekends are shaded with borders; those representing weekdays are clear with borders.
Combining so many beautiful elements of Excel into a spreadsheet so useful was rewarding to say the least. Formula-driven conditional formatting, lookups, ifs, weekdays, moduli. Happy days.
Ian Watkins’ wikipedia entry
Ian Watkins is better known as H from Steps, H standing for hyperactive apparently. I was surprised to see a non-trivial number of people in August (39) happening upon this site directly from his Wikipedia entry. It seems that I’m quoted as a reference (number six), allegedly fuelling rumours of his sexuality. Slightly harsh, given that my post succeeded his coming out on live TV. Still, quite amusing.
Copy paste weirdness
If I open two instances of Excel 2007 and copy a range from one to another using paste special, the destination doesn’t recognise the copied range as an Excel object. Instead of offering paste values, paste formulas (it’s formulae, goddamit), transpose and the like, it offers me Microsoft Office 2003 Worksheet Object, Picture (Enhanced Metafile), Bitmap etc. Copying between two workbooks within the same instance behaves correctly. Bizarre, and rather annoying.
The Citroën C5: unmistakenly German, without the attention to detail
The new Citroën C5 Tourer is advertising itself on billboards as having "504.98 cubic litres of boot space, precisely". I assume they mean litres; or cubic decimetres. Because as far as I’m aware, the cubic litre is not a unit of measurement.
Excel diary: a thing of beauty
Today I created one of the most beautiful spreadsheets I’ve ever created. It should have universal appeal, is simple in concept but more cumbersome than you might imagine to implement.
It’s a calendar.
It shows the days of any year you choose (from 1900 to 9999) in the traditional diary-style monthly view, with days of the weeks as columns, each week appearing below the next. Each month prints out on a different page and it takes into account leap-years, including the less well-known 100-year and 400-year rules.
Here are the 2008 version (53Kb) and the 1997–2003 version (oddly, 212Kb). On the Annual sheet, enter your year of choice. And your more detailed calendar will appear on the Calendar sheet.
Oh and by the way, 31 December 9999 will be a Friday. And what a Friday night it will be.
Pace by distance
I read an article today on the Freakonomics blog about the relative paces of world record-holding athletes over different distances. Its specific focus was on who was indeed the fastest person in the world, and whether the 100m or the 200m world record holder was, on average, faster. (The point is somewhat moot now that Usain Bolt holds both records.) Anyway, I did some analysis.
Below is a chart I put together showing the average speed of different distances’ world record holders over 100m.
Right-click and View Image for a closer look at the chart in FireFox; there doesn’t seem to be an equivalent in IE7. I’ve given cuts of the data every ten years, working back from 2008. For each series, data starts where records begin. The half-marathon has the steepest gradient in recent history, probably down to the event being taken more seriously in more recent years. (For completeness, the 1908 marathon record equated to 24.93 seconds per 100 metres, but its inclusion squashed the scale unnecessarily.) The 60m record is 9.9% slower in pace than the 100m record, but the 100m and 200m paces have pretty much kept in line with one another, the extra distance and the impact of the bend in the 200m being counterbalanced by the impact the acceleration has on the time of the 100m. Below is a closer look at these two.
No conclusions; merely food for thought. Maybe.
Call to action
I received an email from the Royal Mail this morning containing the following text. Nothing more, nothing less.
Dear Dan,
Your Debit card Visa with card number xxxx xxxx xxxx xxxx will expire soon.
Thank you
The Royal Mail Group
(I obscured the last four digits of my debit (lowercase) card number for my own peace of mind.)
The card is registered with them to allow me to pre-pay for self-printed postage saving me many a Post Office line.
My main question is “so what?” What should I do about it? Where is the link inviting me to enter the details of my new card? Oh, and why are they thanking me for my soon-to-expire card?
No thought whatsoever has been put into the content of the email. Where is the call to action? And therefore what is the overall purpose of the email?
Full Olympic medal analysis
So, the Olympics are over. And here’s a full analysis of the 958 medals won.
In terms of the traditional measure of number of gold medals won, China take the honours with 51, followed by the USA (36), Russia (23) and Great Britain (19). The same order is maintained for the top four if you base it on a scoring method of three for a gold, two for a silver and one for a bronze, China scoring 223 points, USA 220, Russia 72 and Great Britain 47. If you forget the medals’ colour and base it purely on the medal count, then the USA (110) and China (100) switch places, Russia (72) and Great Britain (47) remaining in third and fourth respectively.
If instead you look at medals compared to countries’ populations, then it’s a different story. The Bahamas’ two medals equate to one for every 165,500 people. Jamaica come second (eleven medals; one per 246,727 people); Iceland third (one medal; one per 316,252 people), with the top five rounded off with Slovenia (five medals; one per 405,800) and Australia (46 medals; one per 444,221). The least successful of the 87 medal-winning countries by this measure were India (three medals, one per 376,622,051), Vietnam (one; 85,262,356), Egypt (one; 75,231,000), South Africa (one; 47,850,700) and Indonesia (five; 46,938,799).
Excluding relatively trivial examples (denominators less than seven), the most successful countries in terms of percentage golds were Ethiopia (57%; four of seven), Jamaica (55%; six of eleven), China (51%; 51 of 100), Romania (four of eight), the Netherlands (44%; seven of 16), South Korea (42%; 13 of 31) and Great Britain (42%; 19 of 47).
Ben Dirs: crimes against the apostrophe
During the BBC’s online Olympic coverage this morning, there was the following update at 10.45:
1045: And we’re off – Sarah Stevenson versus Maria del Rosario Espinoza of Mexico. Can the Doncaster lass keep her head while all around her are losing theres’? The 20-year-old Mexican is the current world middleweight champion, a title she won in Beijing last year.
Fortunately, they "corrected" it quickly to:
1045: And we’re off – Sarah Stevenson versus Maria del Rosario Espinoza of Mexico. Can the Doncaster lass keep her head while all around her are losing theirs’? The 20-year-old Mexican is the current world middleweight champion, a title she won in Beijing last year.
A couple of heinous errors from Ben Dirs, whose name is itself a stroke of genius.
Signs of masculinity
Steve recently remarked, and indeed misquoted, our midweek conversation about the two signs of masculinity. His post details the two signs as shed ownership and the ability to bear children. Allow me to correct him on both counts. The building of a shed is the sign, not merely shed ownership—shed inheritance certainly doesn’t count. And I’m not sure that a man’s ability to bear children is the sign, more his fathering of them. I certainly don’t have child-bearing hips.
Both tongue-in-cheek; but important distinctions to make.
Oh, and here’s my new shed, built single-handedly.
Quote of the day
"I am absolutely delighted to have won the award. I know self-harming is not funny but it’s just a joke, so I’m not going to beat myself up about it."
Zoe Lyons in acknowledging her winning the funniest joke in Edinburgh award, one about Amy Winehouse’s self-harming.
Lightning Bolt
A phenomenal graphic courtesy of the New York Times’ Freakonomics blog showing the 252 fastest 200m sprints of all time.
Here is the fully interactive version.
Bolt and Johnson are outliers to a ludicrous degree. There are three whole tenths of a second devoid of any times—from 19.32s to 19.62s. If you except Johnson’s previous world record time, Bolt is 1.63% quicker than the next best time ever (Tyson Gay’s last year). Over such a short distance, that’s remarkable.
When extrapolated to the marathon, it equates to a margin of 2m 4s compared to Haile Gebrselassie’s time of 2h 4m 26s in Berlin in 2007. Three athletes have managed that feat, Khalid Khannouchi doing so twice. On the women’s side, Paula Radcliffe’s 2003 London marathon (2h 15m 25s) was 2.43% quicker than the next fastest, Catherine Ndereba’s 2001 Chicago effort. Again, a ludicrous margin and a record that will surely be tough to beat.
Interestingly, the late Florence Griffith-Joyner’s 200m time of 21.34 during 1988’s Seoul Olympics is 1.39% quicker than her fastest rival, Marion Jones’ 21.62 recorded in 1998 allegedly before her drug phase. Whether indeed either of these times were drug-assisted will never be known.
Boy Meets World
Congratulations to Corey, who passed a bunch of GCSEs today with a load of As and A*s. Not sure what A*s are (they didn’t have them in my day, let me tell you), but apparently they’re even better than As. Oh, and apparently he didn’t do PE GCSE (who’d've thought there was such a thing), which makes him more intelligent than his peers, even if it also means he’ll weigh 400lb (182kg) by the time he’s 22.
This is a good blog. Honest!
My blog is good. It’s official. Please don’t jump to comment in disagreement just yet—let me explain.
Here is Merlin Mann’s list of things that he believes make for a good blog. And I have a lot of time for what he says on 43 Folders. Below is my interpretation of this blog against each. I think I can put a tick against most of them.
- Good blogs have a voice: I like to think that my blog represents me. My posts carry my name, and I hope that some of my personality comes across in the 1,230 posts that precede this one
- Good blogs reflect focused obsessions: I think that’s a given. Maths, grammar, Excel draw some healthy/unhealthy levels of passion and obsession, while I hope I enthuse about the other stuff too
- Good blogs are the product of attention * interest: for the reader that is. If I maintain your attention in a subject you’re interested in, then I’ve done a good job. Hello? Are you still there? Only you can be the arbiter of my success on this one
- Good posts are made of paragraphs: this refers to the crafting of posts. I fail a little here. Often I just spout. I don’t take sufficient effort to craft humour, interest and structure into my posts. I’m not saying there’s no effort; just that there could be more
- Good non-post blogs have style and curation: this doesn’t apply to me, but to pictorial and video blogs, and those with weird formats. Mine’s most definitely a post-based blog
- Good blogs are weird: tick. Next!
- Good blogs make you want to start your own blog: again, only the readers can say. I do know of one blog that was started off the back of mine
- Good blogs try: I certainly try. I try too hard sometimes. But I try
- Good blogs know when to break their own rules: I have no rules, so cannot break them
BuggaLove
BuggaLove: surely an ill-advised choice of brand name for a company that makes kids’ stuff.
19 July 20XX
I received an out-of-office reply confirming that my intended recipient would be back in the office on Tuesday 19 July. He either came back in 2005; or will be back in 2011. Not sure which.
Expresso my ass
Although some dictionaries, including Merriam-Webster, accept expresso as an alternative spelling and indeed pronunciation of espresso, to me it is the mark of a heathen. And here’s a previous post on the correct pronunciation of latte.
Gold rushers
Not only have I been closely watching the relative positions of the countries in the Olympic medal table, as Great Britain first climbed, then eased slowly away from Australia, Germany and Russia to cement their number three spot; I’ve also been looking at the relative split of medal metals won by each country.
As an aside, apparently the USA insists on sorting the medals table by total number of medals won, putting them in the lead with 79 medals over China’s 76. The traditional sorting method has always been by the number of golds, with ties settled by number of silvers, then by number of bronzes. This can appear harsh, with Cuba (1, 5, 5) trailing Georgia (2, 0, 1), and Hungary (0, 4, 1) trailing Tunisia (1, 0, 0). But it strikes me as a more sensible approach without resorting to putting a subjective weighting on the value of each metal. (As a further aside, assigning 3/2/1 points for G/S/B would still leave China ahead of the USA at the top. But Russia would leap to third ahead of Great Britain.
Back to the point: relative medal counts. (All data taken as at end of today’s events.) Once a medal is on the cards, which country is the most hungry for gold? The quotient: # golds/# medals.
There are six spurious examples, Bahrain, Cameroon, India, Panama, Thailand and Tunisia all having won a single medal, it being gold. Each of them therefore has a 100% record in converting medals to gold. But if we instead look at the countries with a sufficiently high numerator to deem them worthy of analysis, then it might be best to take the top 16 countries in the standard-sorted medals table, all of which have won three or more golds.
The chart below shows how the medals break down for those 16 countries, appearing from left to right in the order in which they appear in the medals table.
Slovakia in 16th is hungriest for gold, with a 75% (three of four) conversion rate—too few medals to count, perhaps. China sits second at 57%, impressive given its huge denominator; Romania third (50%) and Great Britain fourth (48%). At the bottom of the scale, France has converted a mere 14% to gold, then Russia (24%), Ukraine (29%), and Netherlands and Australia (31%). The USA has a pretty even spread of medals; 26 gold (33%), 26 silver, 27 bronze medals. Phelps’ absence would have brought their quotient down to around 25%.
Perhaps these figures show countries’ appetite for success; although they will be skewed by events in which multiple athletes from a single country win medals, the Women’s 100m for example, a clean-sweep for Jamaica.
Par: it’s all relative
Kelly Sotherton gave a sub-par performance in the long jump on day two of her Heptathlon bid. Which will disappoint her no doubt. But when Padraig Harrington gave a similarly sub-par performance last week in the golf, he was giddy with joy.
Pool length
Has anyone measured the length of the swimming pool in Beijing’s Cube? Michael Phelps seems to be intent on breaking eight world records on his way to eight golds, and this morning bagged his sixth of each. Maybe it’s only 48m long. Could someone check for me?