patboule.com

Well, it was so nice to see so many ‘shout outs’ from J’s buds in the St. Matt’s yearbook. As you know, I’ve always tried to guide the ‘youth of today’ with wise words if not deeds, and it’s nice to see this effort appreciated.

After 9 years of silence on this blog – here’s a St. Matt’s focused post for everybody joining the party.

Notable accomplishments of the last decade was multiple top 5 finishes, and winning the St. Matt’s Trivia Fundraiser night once. That year they wouldn’t announce the name of our team as the winners, cause we called ourselves ‘The Abdicators’. Oh, the scandal, gotta love Catholic schools! Aydan did a great job last year as trivia emcee, but we only came in second (to Max’s dad!) so clearly he was not sharing any answers with us.

I am also a Cory Ell superfan – really appreciate all his live music during this COVID crisis, and the fact that he can transpose musical keys in his head…that dude has put in his 10,000 hours! Cory and I went to St. Matt’s when it only went to Grade 10, and when it only had Touch Football (won the boards that Grade 10 year, btw).

Anyways here’s a nice shot of a recent getaway – all the best to you lads in your future endeavors.

“Just get us back in the van, dammit!” – Ragged Falls near Algonquin

There are 1.1 million unapproved comments (no joke, the bots love me) on this blog – so if you have something to say – reach out via my firstinitlastname@gmail, or follow me on Twitter @patboule 😉

Technical aside- I had to upgrade WordPress to get this thing to work again, and renew the domain name…so thanks for that – an hour and 50 bucks I’ll never see again. You are worth it!

Check back often.

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.

Every time I visit The Weather Network’s site at www.theweathernetwork.com – 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 www.theweathernetwork.com 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…

• Boston Bar, British Columbia, Canada

• Boston, Georgia, United States

• Boston, Indiana, United States

• Boston, Massachusetts, United States

• Bostonia, California, United States

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 pboule@gmail.com

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 CommonSenseCrossings.com

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?

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:

=vlookup(some_cell,up_left$corner$:bot_right$corner$,OFFSET,false)

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:

=column()

Solution 2 The Table:

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

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
http://www.cbc.ca/video/radio-popup.html?networkKey=cbc_radio_2&programKey=eastern

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 of the awesome Weed Hound dandelion puller at Cool Tools got published today! Exciting!

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)

=SUM((‘[KSB1-2009-2010.xls]Sheet1’!$AA$2:$AA$4006=I4)*(‘[KSB1-2009-2010.xls]Sheet1’!$AL$2:$AL$4006=J4)*(‘[KSB1-2009-2010.xls]Sheet1’!$AO$2:$AO$4006=”Jan”)*(‘[KSB1-2009-2010.xls]Sheet1’!$AG$2:$AG$4006))

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.

=IF((LEN(I4)>4),TEXT(I4,”?.?”),TEXT(I4,”?”))

• 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!

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.

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.

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.