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.

World record paces

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.

World record paces: 100m and 200m

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.


Next Page →