From colouring book to art

September 9th, 2011

In 2007, we spent National Aboriginal Day in Pacific Rim National Park on Vancouver Island.

At the main park centre, there were these events going on, including a colouring table for the kids.

When my kids were done, I went over to the table, and was gobsmacked at what somebody had done with the simple typical whale outline – shown below.

I only took this photo of the whale, but if I’d seen the creator – I think I would have bought it. Whenever I stumble upon it in my photo library, it makes me happy.


How and why sucks

October 27th, 2010

Every time I visit The Weather Network’s site at – I leave pissed off, even if I eventually find what I’m looking for.

I always figured that they were just clueless and couldn’t implement basic search, but after thinking about it some more (and putting on my tinfoil hat, natch) I think that it’s a clever strategy on their part.

The search interface on The Weather Network’s site is one of the worst I’ve ever seen.

Here’s why! Every page you hit on their site throws you three new ads, and some website geek has engineered their site to make sure you visit the most pages possible on your search to find the weather in some city.
The more pages you need to go to…the more “visits” they have, and the more ADS they get to serve you.

Let me give you four shining examples of how crappy The Weather Networks website is:

1)      Search for city,state (two letter) combinations does not work. Example: Boston, MA search FAILS outright – but you get 3 NEW ADS! Ditto for Toronto, ON. Try it for your city!

2) Even a search for BOSTON, MASSACHUSETTS asks you to confirm if you want the one in the United States.

3)      Getting even more absurd, so does a search for BOSTON, MASSACHUSETTS, UNITED STATES.

4)      Search for common cities by name only fails. It takes you to a new page (NEW ADS) and asks you to confirm which city you really mean. Example: On a “Boston” only search…

I think they’re trying to game their advertisers by having single users have to click through more pages than necessary (and therefore, multiple ad throws), in order to find the information they are looking for.

What TWN may not understand is that upsetting your users for a commodity site like weather will just drive people away. Advertisers on this site like Royal Bank, Kudo, Bose and Disney should know about this underhanded strategy.

Hey – Weather Network – just take me to the effing page already, and save your extra 6 ads for somebody else. Also – since I’ve gone off on you already, how about showing me more than the 24 hour forecast, in case I’m planning for beyond tomorrow.

I feel better now.

Are there any other popular websites out that abuse the patience of their users like this one? Let me know at

5 lessons for running and responding to political e-surveys

October 18th, 2010

If you’re wondering where Ottawa mayoral candidates stand on the inter provincial bridge file, check out the results of this survey I just published here at

Here’s 5  lessons I gleaned from the experience of coordinating this survey:

  1. Little fish catch big fish. Early on – our little community group wasn’t getting ANY uptake from the major candidates – but as soon as we had an answer from a credible candidate, we were able to use the fact that that a major candidate answered as leverage to get other big name candidates to weigh in. Even the last straggler finally bowed to pressure that they were the only candidate not to respond.
  2. Be personal. The best answers we got were written in the candidates own voice – in many cases from their own email accounts. They were conversational, and showed that the candidate knew the issue, and was comfortable discussing it. Kind of like what I strive for in this blog!
  3. You don’t need to answer THE QUESTION. The most savvy candidates did not bother answering our slightly pointed question directly  - they sent us a summary of their position on the file, and sprinkled it with their other initiatives. They probably knew that our volunteer organization would publish damn near anything they sent us, just to make sure that we could say we had a statement from all candidates.
  4. Don’t torpedo your own message. One candidate, who is anti-bridge, seemed to treat the survey like a high school essay. When asked “ would benefit from the bridge”, they answered that it would be useful for people with cottages in Quebec. So – two points for the essay response, but zero points for remaining on message. Can you see the headline now: “Candidate X: Bridge will benefit cottagers.”
  5. The margins will distinguish themselves. In our case – two questions pretty distinguished the mainstream from the margins. Just read the responses and you’ll get it pretty quickly.

What’s the most surprising answer a politician ever gave you, and what did you learn?

Under your nose: Excel, VLOOKUP and Columns

September 22nd, 2010

Sometimes searching can be tough, when what you’re looking for is trapped inside the language fundamental to the thing on you are searching about. It’s true, sometimes you can’t see the forest for the trees.

A mental block I have with Excel is trying to remember what number a column translates to when your spreadsheet gets really, really wide. Like in the A->CG range. I know that A is 1, and AA is 27…after that things get sketchy.

Who cares? You need this info when you try and use an offset in a vlookup calculation:


My initial fallback – the Google – would be to search something like:

excel column formula

which of course gives totally useless results – because the search terms themselves are fundamental to Excel: they are the atomic building blocks of Excel. I’d may as well have searched “excel, row, column”.

I’ve run into similar problems trying to search historical info on IP address allocation. Since every node connected to the internet has an IP address, so there’s alot of meaningless hits. I want info on the concept, not the data.

The whole point of this post was for me to document the solution to my column OFFSET problem, so that when I forget it next, I can Google it, and at least hit this page.

Solution 1: The Excel Column Number Formula:


Solution 2 The Table:

It’s like a formula, but for lazy people:

Column Heading
A- B- C- D-
A 1 27 53 79 105
B 2 28 54 80 106
C 3 29 55 81 107
D 4 30 56 82 108
E 5 31 57 83 109
F 6 32 58 84 110
G 7 33 59 85 111
H 8 34 60 86 112
I 9 35 61 87 113
J 10 36 62 88 114
K 11 37 63 89 115
L 12 38 64 90 116
M 13 39 65 91 117
N 14 40 66 92 118
O 15 41 67 93 119
P 16 42 68 94 120
Q 17 43 69 95 121
R 18 44 70 96 122
S 19 45 71 97 123
T 20 46 72 98 124
U 21 47 73 99 125
V 22 48 74 100 126
W 23 49 75 101 127
X 24 50 76 102 128
Y 25 51 77 103 129
Z 26 52 78 104 130

How to record CBC radio shows on your PC

June 9th, 2010

Here in Canada, I regularly drive through dead zones of radio coverage. Before podcasts were everywhere, I got the notion to record some favorite radio shows from my PC to listen to while we went through these dead zones.

Fast forward a couple years: now there’s a decent library of Vinyl Cafe, Randy Bachman’s Vinyl Tap, Gregory Charles (cancelled – sigh!) to pick and choose from to fill dead air during those drives. CBC doesn’t officially podcast any of these shows (or not in their entirety because they play – gasp – music).

Recently I had to tweak my settings to get it working again – and to start recording the Strombo show -  so I thought I’d share how I do it.

There’s two parts to recording something online:

  1. Getting your computer to actually record the sound. I tried a few options here, and eventually went the low cost commercial software route on this one and bought a standard license to Total Recorder @ $17.95, after first checking out the trial version to ensure I could get it working. This program basically sits as a virtual recorder on top of your PCs speaker. If you can play it on your PC – this app will record the sound, and can save it as an MP3. The basic license comes with a scheduler – so I just set it up to record at the times of the shows I want.
  2. Getting your computer to actually play the radio station that you want. You need this, or else Total Recorder is just recording silence. On a WinXP PC, I use a “Scheduled Task“, under Windows Control Panel  to fire up the link to the streaming radio once a week, and time it to start when your Total Recorder schedule kicks in.

Setting up your scheduled task - like so many other things in life, the trick here is to get it working once.

  • First, make sure the radio stream will play through your browser. Go into your Firefox browser, click through to play a given radio stream, and copy that URL – you’ll need it later.
  • Then setup your “New Scheduled Task” to run Once, in about 10 minutes to test it out, and pick your Mozilla browser as the program to run.
  • Now modify the URL after your browser to include the URL of the audio stream that you want to open.
  • Once you check it’s working – modify the Scheduler in the advanced tabs to run weekly, and to run and wake your PC even when it’s asleep, and to stop after your show ends.
  • Finally, turn down your speakers so you don’t scare the cat when your PC wakes up at midnight to record the West Coast feed of the Strombo show.

Here’s what worked for me – inside the little Scheduled Tasks window:

C:\PROGRA~1\MOZILL~1\firefox.exe -new-tab

Some final hints:

  • Don’t start your recordings at the top of the hour exactly or you’ll catch the news. Boring – and freaks you out when you hear 2 year old news later on.
  • If your radio offers multiple timezone streams – take advantage of it so your PC is recording off peak hours.
  • Total Recorder is also handy if you want to convert some of the great concerts on CBC Radio 3 into more portable formats for your personal use.
  • If you get bogged down with TR’s default file naming scheme – search out a nifty tool called Oscar’s Renamer that’ll let you bulk rename your files so you know what stuff you recorded later on. Otherwise, just bundle your recordings into playlists by show.

Let me know what you think!

My review made the Cool Tools blog!

May 18th, 2010

My review of the awesome Weed Hound dandelion puller at Cool Tools got published today! Exciting!

Pat’s guide to troubleshooting Excel

May 3rd, 2010

I was helping a friend get a complicated array formula working with Excel the other day. She had a sample formula taken from the Web and was pulling her hair out trying to get it working. Now, I know a fair bit about Excel, but I’d never worked with array formulas  before.

In short – they’ll act on a a whole range of cells in a single formula. When you’re done editing them, you hit Ctrl-Shift-Enter to show they are array formulas, and Excel adds in some {} brackets around it. That’s also why they’re called CSE formulas.

Despite this – we had the formula working in pretty short order, cause we applied a bunch of Troubleshooting 101 techniques to the problems.

  1. Break the problem down into component parts.
  2. Get something working, anything!
  3. Reduce the range so a person can see it
  4. Check as you go – force some matches
  5. Finally, focus on the hard stuff
  6. Don’t forget the remnants
  7. One, two, infinity. Expand your solution.

Here’s the problem in excel speak – for every row in a huge long range, we were trying to sum everything that matched three criteria:

  1. a shopping cart number (column I)
  2. an approval number (col J)
  3. a month (hardcoded)
  4. an invoice column (AG)


Here’s what we did:

  • Break the problem down into component parts.

We’re trying to do a sum based on number of multiplications based on matches within different columns. If there’s a match, it turns up as a “1″…so it’s like doing a conditional sum on all rows that match. Looks doable – let’s tackle it in parts. Let’s try an easy sum (non-array formula) first, just to get the juices flowing.

Our first problem was that the template she was using had every cell formatted as text – which can cause you to poke your eyes out if you try and type a formula in it. In this version of excel, even changing the cell format to general afterwards won’t kick your formula into working. You need to change format, then delete and retype your forumla. Ouch. I was starting to understand her frustration.

  • Get something working, anything!

Next problem – get ANY array formula working. Neither of us had worked with them before – let’s try a simple one just summing ANYTHING before boiling the ocean.

So we started with a simple one term array formula summing everything that matched I4 – it gave us a result – and we checked it against a pivot table so we knew it worked.

  • Reduce the range so a person can see it.

Now we added the other terms back in – but got a bunch of N/A results. Hmm. When we went into Evaluate Calculation pulldown, all we saw was a long series of 0’s and N/As. Time to reduce the range from a 4000 row range to something manageable that a human can get their head around – like 6.

So we changed our ranges down to 6.

We still got the N/A result – but we could now see the problem was with the second term in the equation – the I4 lookup. So we took that term out. We now saw numbers – but they were all zeros.

  • Check as you go – force some matches

Once we had the range down to 6, and the wonky term taken out, we were just seeing zeros – no surprise there were no 2 term matches in the 6 rows of data. So we edited this data to fake it – just as a check. Ok the reduced match is working.

  • Finally, focus on the hard stuff

Now the only thing left was the wonky I4 match – which turned out to be the result of an evil “number stored as text”, which in Excel looks like text, but does not act as text when used as part of a formula. Even the Excel MVPs don’t write much about the subject – but Excel must do something funny internally to these cells just in case users try and recast them as numbers later on.

However – we were able to force the text to be treated as text, for formula purposes, with a formula called, oddly enough: TEXT.  As my old computer lab buddy Art used to say, “Funny how it’s always the last thing you try that works”.

  • Don’t forget the remnants

The above solution worked for most of our approval numbers, which were integers, but we needed to wrap that statement in an IF to handle the remaining numbers which were one digit decimals – because the text function was actually pretty harsh about how it was going to display different numbers.


  • One, two infinity  – expand your solution to cover the whole problem

Time to put a bow on the formula – re-expand the ranges, fix the master data you changed so you’d have an easy match. The final thing we did was anchor the lookups so that when the formula got copied over to sum different things, it would still work, and de-referenced the month to a header cell at the top of the column.

So it turned out to be 7 or 8 steps peeling the onion. But the onion is peeled, baby!

iPod touch meets intellivision controller

April 8th, 2010

In my last post, I was talking about how gutsy it was that Logitech would release a trackpad application for the iPhone for FREE – basically cannibalizing it’s own market.

Later in the week I was wondering how the heck Logitech would make any money on this…and it hit me.

It’s not just about turning the iPhone into a simple trackpad – it now becomes a PC peripheral with custom buttons, with infinite overlays, dual screen potential, and an accelerometer.

In short – it’s now as versatile as the intellivsion controller in the 1980’s – where you just slid in your custom plastic sheet and the keypad became a custom baseball controller or or whatever.

Thanks to Tom's Heroes for the overlays

Oddly reminiscent of an iPhone, huh?

If Logitech succeeds in becoming the defacto standard for iPhone to PC communication as trackpad plus – there’s probably a ton of different apps that will license this underlying capability as smart handhelds become ubiquitous.

Everything from slideshow controller to media center remote control is on the table for Logitech now.

And I think that’s how Logitech is going to make some money on this.

Now Apple came very close to doing this themselves when they launched the Remote application that let you browse through your Mac music library – but they didn’t take the complete plunge.

Logitech’s stunning new iPhone app

March 31st, 2010

When I first heard about Logitech’s free Touch Mouse app that turns your iPhone/iPod Touch into a wireless trackpad (and keyboard) for your computer – I had two thoughts.

First – I laughed because I was telling my dad a couple months ago that this was a natural future use of the iPod touch, after seeing him fumble with a wireless mouse, a laptop and a USB dongle.

Second – Think about what guts it took the Logitech suits to embrace this possiblity, build and support an application for it, and release it FOR FREE.

What Logitech has done is abstract the mouse (their main business, at least historically) OFF of the mouse, onto a third party device. The kicker – they’re giving it away for free!

Whenever we came up with an idea like this back in my Telecom “business unit” days, we’d get stern business advice about cannibalizing our current revenue stream, how current customers would get pissed off, and how there was no money to be made in “free”.

Then somebody else would go ahead and build and scale the same idea which have varied from Skype, to wholesaling bandwidth in a global WAN.

I’m very impressed that Logitech had the guts, and the balls to be their own “somebody else”.

I’m swimming a similar idea upstream at work at the moment…maybe someday I can tell you how it pans out.

Hang memories on your Christmas tree

January 20th, 2010

Thought I’d share a tip we use around here that makes me happy at least once a year.

When traveling, we follow a pretty strict “no souvenirs” policy, with one exception – we try and pick up something we can hang on the Christmas tree, to remind us of the trip.

Key rules:

  • It can’t be an ‘official ornament’.
  • It gets stored when the tree comes down.

In the past we’ve repurposed totem keychains from Vancouver, thimbles from NYC, and a baby sock with a Cubs logo on it. There’s even a brass armadillo from Austin that’s hanging courtesy of some old twist ties.

Here’s why it works:

  • It gives you something fun to look for when you’re on vacation.
  • It makes you feel good when you decorate the tree every year. You think back on these fun trips, versus thinking,  “look, a shiny ball”.
  • It gives visitors to your house something to talk about.
  • Perhaps best of all…it gets stored away most of the time – so it’s not cluttering up your house!

Speaking of happiness in general – there’s a great book called Stumbling on Happiness that’s essentially a summary of various research studies about the kind of choices that typically make people happy – the results will almost certainly surprise you.

Here’s one tip from the book: If you have a favorite dish at a restaurant – don’t waste your time ordering other items to see if you can find a second favorite at that spot.  Odds are low.

Another tip: over the long term, you’re more likely to be happy with a purchase if you bought it from a “NO RETURN” policy…how counter-intuitive is that??