Thursday, March 07, 2013

CanalOmeters for Tablets

A small number of enlightened people have taken up my CanalOmeters in the past and been kind enough to say they were useful.  Certainly I have enjoyed making and using them.  For the uninitiated, they are a simple calculating device for working out long it might take to cruise between two points on a canal journey. They look like this

Follow my link on the right hand side of the blog page to find out more.
Now, having got a nice low tech cheap as chips solution that works, why on earth would I bother to spend time making a digital version?  After all didn’t the data come from CanalPlan in a digital table format?  Well, . . .
a) The cardboard ones are great and I will continue to use the ones I have and maybe make a few more, but the formatting of each one in Excel is something of a pain.  When I am in practice I can do one in about half an hour, but next time I forget and it takes me ages.  Then I get a new computer with a new version of excel and it all goes haywire.  Grrrrr.
b) the cardboard ones are limited in how many place names I can squeeze in
c) If you have a tablet computer with a good internet connection you could use (the rather wonderful) CanalPlan on the move, but bandwidth along the canal is unreliable and using CanalPlan can be fiddly on a tablet.
I did make one for Smartphones a while back, but in truth the small screen size made it a bit too fiddly to use in anger.  So when I got an iPad mini, I thought I’d have another go, and what I have come up with looks like this.
The spreadsheet contains a list of all the places en route (bar individual locks within flights).  All the user has to do, is put a T (for To) in the left hand common next to the name of the place they are going to, and an F (for From) against the start place.  The T and F can be in either order. The Green box at the top then automatically shows both place names and the Locks, Miles and Hours/Min cruising time.  Simples.  Of course if you get there quicker or slower, that would be because your speed is not the same as we use for calculation purposes, which is generally 3mph.
I’ve been using an Excel compatible iPad App called Sheet2HD, which was either free or dead cheap, I can’t remember which, but I think any half decent Excel compatible sheet would do it, either on iPad or Android.
To use one you need hardly any spreadsheet expertise, just how to open it and type the T and F in at the right spot. ( and to delete them ready for the next go). To make one you need a little bit of spreadsheet knowhow, but not a lot.  The data can be cut from CanalPlan and pasted in quite easily once you get the hang of it.  The calculations are of course dead simple, subtracting one number from another.  The only bit that you have to learn is how to deal with the Hh:Mm time format.
So to recap the benefits over the analogue cardboard version
a) Contains all places en route
b) Quick and easy to develop your own for your own routes.
and over CanalPlan, just that you don’t need to be on line, and it’s quicker to use once built.
I suspect that like me, you might think the cardboard ones are still best if you can be bothered to make them.  I will be using both.
Now, would anyone like to help me by downloading a copy and seeing if you can make it work on your device.  This could either be a PC or an iPad or an Android device, just so long as you have an Excel compatible spreadsheet programme or App.  I have been struggling all morning to work out how to link them in a downloadable form to this blog page and I’m interested to see if it downloads successfully.
All you have to do is click this link and it ought to download.  You then need to start it up in your spreadsheet .  If you do, please send in a comment with any feedback.

PS if this one works and anybody wants more, I have several other routes done already.


Sarah said...

I'll give it a go, and I am the world's biggest hater of spreadsheets. But is £3.99 really next to nothing? Bear with me while I try a free one...

Sarah said...

Goodness, well, I have successfully opened your thingummy in a free app called iSpreadsheet, but I don't seem to be able to edit it :-(

Sarah said...

It works in proper Excel on the PC at work, but then I expect you knew that.

I am gobsmacked at your ingenuity anyway.

Neil Corbett said...

Hmm thanks for trying so promptly Sarah. I'll wait to see if anyone else has problems editing. If so, it's back to the drawing board. These simple things are always so flippin complicated.

As to "next to nothing" £3.99 is a hugely cheaper than Microsoft's £70 for a home/student copy of Excel!

Graham said...

Hi Neil,

I've opened it on an ordinary PC desktop running Win 7 & Excel 2007. This may not be the real test you wanted but it opens and functions perfectly.

I'm not too hot on spreadsheets but could other routes be made by saving this one under a new name and cutting & pasting from CanalPlan?

John said...

Hi Neil,

What a great idea. Downloaded and worked perfectly on my works PC, but that maybe because I have the same version of excel on my PC as you (microsoft 2010).

It might be better if you were to save the spreadsheet as a CSV file which previous versions of excel can open, without the use of the Microsoft compatibility pack (which some people may not have). I would also think that other spreadsheet software might be able to open a CSV file easier than a XLSX one. The only issue may be that some functions in the spreadsheet may not copy across, but a quick look shows only the VLOOKUP and ABS functions being used which shouldn't be a problem.

An immediate use I can see for this whilst on a cruise is if you're delayed for some reason and have to stop for the night earlier than planned. The spreadsheet could be used to see how long you would need to cruise the next day to get back on track.


KevinTOO said...

It works for me :)

I'm using a desktop PC on Windows XP & Excel 2003!

Well done :)

John said...

Hi Neil,

I've just downloaded the link to my Android (ver. 4.0.3)LG smartphone. Unfortunately none of the place names or any text was transferred correctly. All the cells with text in them were converted to numbers. The spreadsheet came with the phone and is part of the Polaris office suite.

I experimented with entering the 't & 'f' in various cells in the appropriate column and all the calculations worked OK as far as I could see. Hope this helps.

I must admit though that I don't think I'll be using it on the smartphone, the screens too small. (I use my notebook on the boat and will load the spreadsheet on that).


Halfie said...

I was initially confused by your photo, until I realised that the "minutes" column had been cut off. And I'm so used to reading "04:35" as minutes and seconds that that threw me for a bit!

Is there a facility to change the parameters, such as speed? I could never maintain 3 mph. I find that 2.5 mph is a good average of passing moored boats and cruising open stretches.

I haven't tried this on a "mobile device" as mine makes and receives phone calls and text messages. And that's all. (Oh - it has a torch and an alarm clock too.) I know, I'm some way behind the times ...

Deb said...

My iPad automatically opened it in Numbers (which was already installed) where it worked perfectly - but Numbers is not a free/cheap app.


Eddie said...

I've downloaded it to my pc using open office which is free. At first it was a read only file so i did as John suggested and converted it from .XLSX to .CVS and it works fine. Great idea i would love that for our summer cruise from York to Lapworth.

Oakie said...
This comment has been removed by the author.
Oakie said...

Hi Neil, It downloaded to my PC fine, but what do I do after entering the From/To boxes? Also how do I save it to a CSV file? I tried it with a CSV extension, but XLSX stays there as well! HELP! I don't use databases very often as you can see, so excuse my ignorance.

Neil Corbett said...


I hope I have understood your message correctly. Not sure that you are doing it right. You don't type anything into the green boxes at the top. What you have to do is type an F or a T in the cell in the column to the left of the name of the place in the big list. Then the green boxes complete themselves automatically.

When saving, if you want to save it as a CSV, use Save As. You should then get a choice of option formats from which you select CSV. Then give the file a name and that should be it.

Write back again if you need more help.

I am in the process of writing out a step by step set of instructions for how to extract data from CanalPlan and put it into a template of my spreadsheet for whatever journey you like. There is a small problem with CanalPlan which needs to be fixed first, then I can publish.

Watch this space.