Passage planning on a spreadsheet

morgandlm

morgandlm

Well-Known Member
Joined
3 Mar 2005
Messages
600
Location
Essex
Visit site
I am in the process of developing a spreadsheet to use as a passage planning tool to do what the RYA teach geometrically for course-to-steer and passage times on a chart. By breaking a passage into sectors subject to a series of tidal diamond areas, the spreadsheet can calculate the effect of the tidal streams at different states of the tide. The worst part is manually entering tidal stream data for each sector. I am using the following simple formula:

Speed-over-ground = boat speed minus the tidal stream x COS ‘angle’ for any given hour.

The 'angle' is the difference between course to be steered and tidal direction for each hour. This works really accurately when the tidal stream is either the same as or is 180 degrees from the course. It is less good when the tidal stream is across the course since COS 90 is zero and yet that stream does still affect the boat speed. I have learned that aviation uses a more complicated formula for calculating speed over the ground taking account of air speed, wind speed and direction but I am not confident that this will translate well to boat speed and tide where the values are generally closer.

Can anybody suggest a better formula for making this calculation?
David Morgan
 
If you can get anybody or device to helm to that degree of precision please invite me along for a days sailing as I'd like to see such skill.
 
morgandlm said:
I am in the process of developing a spreadsheet to use as a passage planning tool to do what the RYA teach geometrically for course-to-steer and passage times on a chart. By breaking a passage into sectors subject to a series of tidal diamond areas, the spreadsheet can calculate the effect of the tidal streams at different states of the tide. The worst part is manually entering tidal stream data for each sector. I am using the following simple formula:

Speed-over-ground = boat speed minus the tidal stream x COS ‘angle’ for any given hour.

The 'angle' is the difference between course to be steered and tidal direction for each hour. This works really accurately when the tidal stream is either the same as or is 180 degrees from the course. It is less good when the tidal stream is across the course since COS 90 is zero and yet that stream does still affect the boat speed. I have learned that aviation uses a more complicated formula for calculating speed over the ground taking account of air speed, wind speed and direction but I am not confident that this will translate well to boat speed and tide where the values are generally closer.

Can anybody suggest a better formula for making this calculation?
David Morgan
Click to expand...
Yes, point the boat in the rough direction and run EPs as you go.

It is not an exact science.
 
Me ... I'm a Beer Mat person for this stuff ... actually a piece of graph paper and ruler / compass to draw the angle.

I do have an electronic Nav calculator, Ti59 + Nav Chip - but time I have entered the numbers - I can do it just as quick on paper ... or on the chart.

I'll see if I can find the Nav Calcs book - it has all the formulae in it for all its routines ... then post up if I find it.
 
morgandlm said:
I am in the process of developing a spreadsheet to use as a passage planning tool to do what the RYA teach geometrically for course-to-steer and passage times on a chart. By breaking a passage into sectors subject to a series of tidal diamond areas, the spreadsheet can calculate the effect of the tidal streams at different states of the tide. The worst part is manually entering tidal stream data for each sector. I am using the following simple formula:

Speed-over-ground = boat speed minus the tidal stream x COS ‘angle’ for any given hour.

The 'angle' is the difference between course to be steered and tidal direction for each hour. This works really accurately when the tidal stream is either the same as or is 180 degrees from the course. It is less good when the tidal stream is across the course since COS 90 is zero and yet that stream does still affect the boat speed. I have learned that aviation uses a more complicated formula for calculating speed over the ground taking account of air speed, wind speed and direction but I am not confident that this will translate well to boat speed and tide where the values are generally closer.

Can anybody suggest a better formula for making this calculation?
David Morgan
Click to expand...
Hi David. Tillergirl might have some ideas.
 
If only one could have a system where the formulae for the calculations were built in to a device, and some means of detecting where the boat currently was, then the operator could just indicate where they wanted to go, and the device would do the sums and tell them which way to point the boat!

I don't suppose it would ever catch on, though. ;)

I used to often navigate a small sailing boat using a fly-swat as a ruler and a chart in a holder on my knees in the cockpit. Angles taken off the compass rose by eye, chart distances judged likewise or by thicknesses of a thumb or whatever. There must have been at errors of at the very least +/- 5 degrees or 10% in my steering the intended course, compass deviation & variation, estimation of wind and tide direction & speed, etc. Perhaps all the inaccuracies cancelled out, because somehow I had no problems getting where I was going.

I think the OP's spreadsheet is an entertaining and interesting challenge, but I can't envisage it being of great practical use.

One of the advantages of the graphic method - pencil on chart - is that (a) it forces you to look at the chart, and (b) if you find yourself getting somewhere other than you intended it is usually fairly obvious from looking at it which of the lines were drawn in the wrong direction or whatever. (It's a bit like using a calculator: if you haven't an idea in advance what sort of number you are expecting as a result, you won't spot a gross error when it appears.)
 
Thanks to all for the mixture of cynicism, constructive comment and dry humour. My starting point was actually to prepare for a single handed passage across the Thames estuary where there are a couple of tidal gates where some advance planning is quite important. An IT based solution for making such a plan is already commercially available and is very well respected by many who have used it. I simply fancied the challenge of developing my own and so far it has indeed been entertaining and interesting. It may prove to be absolutely useless ... however, sharing the process on this forum has had the added bonus of identifying one or two names I will not be inviting for a sail.
DM
 
You have 2 vectors (one for tide speed/direction and one for the boat's speed (through the water) and direction. Each of these vectors is made up of an amount (the speed) and an angle. To sum them you can convert each amount/angle to coordinates (on a polar graph), then sum the coordinates and convert the result back to an amount/angle.

E.g.

Tide - Ts @ Ta (Ts is speed of tide, Ta is tide angle from North).
Boat - Bs @ Ba.

Convert tide amount/angle to coordinates Tx and Ty:

Tx = Ts x SIN(Ta)
Ty = Ts x COS(Ta)

Convert boat amount/angle to coordinates Bx and By:

Bx = Bs x SIN(Ba)
By = Bs x COS(Ba)

Now add the coordinates to get the resultant coordinates Rx and Ry:

Rx = Tx + Bx
Ry = Ty + By

Now convert the resultant coordinate to an amount angle:

Rs = SQRT((Rx x Rx) + (Ry x Ry))
Ra = ATAN2(Rx, Ry))

If you are doing this in Excel then all angles need to be radians, so convert Ta and Ba to radians using the RADIANS function, and convert Ra back to degrees at the end using the DEGREES function.

However... surely for passage planning you want to do the reverse (kind of) of this and enter the CoG that you want to achieve and have the spreadsheet calculate the bearing to steer to and the speed over the ground?
 
morgandlm said:
Thanks to all for the mixture of cynicism, constructive comment and dry humour. My starting point was actually to prepare for a single handed passage across the Thames estuary where there are a couple of tidal gates where some advance planning is quite important. An IT based solution for making such a plan is already commercially available and is very well respected by many who have used it. I simply fancied the challenge of developing my own and so far it has indeed been entertaining and interesting. It may prove to be absolutely useless ... however, sharing the process on this forum has had the added bonus of identifying one or two names I will not be inviting for a sail.
DM
Click to expand...
I crossed the Thames Estuary twice last summer. It is well buoyed and has lots of wind farms, perhaps the easiest bit of navigation I've ever done.
 
wingcommander said:
Let me know if anyone has a formula that anticipates wind shifts.
Click to expand...
Oh that's easy. Come to the Thames Estuary. It's always s*dding Easterlies :devilish:

There are two issues - passage time - easy, get out the spreadsheet: course to steer - get out the chart. Unless you are going to have a passage for one exact hour that coincides with the tide data. But if the passage doesn't coincide with that exact hour........

Let us suppose you are using a passage planning assumption of 5kts STW. But the distance of that sector irrespective of the tide would take say, 1 hr 45 minutes, you might need, depending on the time you are starting, to use part of three hours of tide data: e.g. say for example 20 minutes of -3.00, 60 minutes of -2.00 and 15 minutes of -1.00. Your riming might only need 2 parts of tide data. That is very easy to use a spreadsheet to predict the sector time. You have to 'overlay' each triangle metaphorically into the spreadsheet. But trig won't work for the course to steer unless it is for one hour exactly fitting the tide data.

If you are going to cross the Thames Estuary you are going to have to break down the whole passage into a series of sectors - er, there is a book about that somewhere :unsure: and somebody has created a 540mb Excel spreadsheet to set out all the tables read to use.
 
morgandlm said:
My starting point was actually to prepare for a single handed passage across the Thames estuary where there are a couple of tidal gates where some advance planning is quite important.
Click to expand...

As heavily hinted above, Tillergirl's excellent publication 'Crossing the Thames Estuary' (and very useful associated website) has done all that for you, if you haven't discovered it already.

morgandlm said:
however, sharing the process on this forum has had the added bonus of identifying one or two names I will not be inviting for a sail.
Click to expand...

:D

Oh, hang on! :oops:
 
tillergirl said:
Roger (Moody Sabre) suggested you'd be along with some good comments - thanks. I am familiar with the book you refer to which deals with a whole load of different Thames crossing options. I only wanted to build a single passage plan from The Blackwater via Spitway, SW Sunk, Fisherman's and N Foreland - so just a single route. Perhaps I'm just confessing to being too mean to buy the book! . . . . . but I also fancied the intellectual challenge of doing it myself. My spreadsheet does just what you describe in a series of sectors looking up tidal diamond data as it goes. The compromise the spreadsheet has to make about where and when to switch to a new set of tidal diamond data is no different to what you have to do if doing it "by hand". My effort is a long way short of the 540 Mb of "The Book" but it is only one route, not full of colour and has no optimisation function in it.
Thanks for the input. I haven't given up yet!
D M

If you are going to cross the Thames Estuary you are going to have to break down the whole passage into a series of sectors - er, there is a book about that somewhere :unsure: and somebody has created a 540mb Excel spreadsheet to set out all the tables read to use.
Click to expand...
 
Yeap, just for the sake of the cost of a decent sauvignon blanc in a restaurant :giggle: Since you want the intellectual challenge, I has not supply the formula ? but I will warn you that it is addictive and in a couple of weeks you will be writing this sort of thing:

=IF(AND(Durations!H69="10 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'10 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="10 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'10 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="8 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'8 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="8 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'8 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="7 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'7 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="7 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'7 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="6 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'6 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="6 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'6 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="5 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'5 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="5 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'5 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="4 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'4 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="4 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'4 Kts S'!CT724:CV1444,3),IF(AND(Durations!H69="3 Kts",Durations!I70="Springs"),VLOOKUP(GearsM515!K2,'3 Kts S'!CT2:CV722,3),IF(AND(Durations!H69="3 Kts",Durations!I70="Neaps"),VLOOKUP(GearsM515!K2,'3 Kts S'!CT724:CV1444,3))))))))))))))) ?

There are a couple of easily missed mistakes as well?‍?
 
You must log in or register to reply here.

Share this page

Top