Compass question excel 4

[jhn1611]

Hello,

I came across a previous thread on here which had a very helpful formula to calculate the difference between 2 headings using the MOD function

I work on a vessel and we always have a reference heading of a piece of equipment on the seabead called the 'datum bore heading'.

Now throughout this job we effectively work in a square and the datum bore will always be. FWD, STBD, AFT or PORT in relation to vessel heading

Im trying to wirte a formula where I imput vessel heading and datum bore heading and it tells me FWD, STBD, AFT or PORT

Now FWD and AFT are I can manage. If the difference between two headings is 45 or less then its FWD. if its greater than 135 its AFT

Between 45 or 135 is PORT or STBD depending on which way vessel turns. If vessel turns clockwise on compass then bore heading on PORT and and if anticlockwise its STBD

Anyone know how to write such a thing on excel?

Thanks

 

[MintJulep]

[pre]heading 1
true bearing to target 359
Relative bearing 358 =MOD(360-MOD(B1-B2,360),360)
Relative bearing 2 =MOD(B1-B2,360)
[/pre]

 

[IDS]

But Heading-Bearing = -358 = 2, so the single MOD is correct.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

Yes, there is a 2 degree difference. But the side of the vessel is lost.

For relative barring, 0 is the vessel bow, with relative bearing increasing clockwise.

So, with a vessel heading of 1 true (or magnetic)
Bearing to target 356 true (magnetic)

Then the target is 358 degrees CW from the bow. Yes, that's the same as 2 degrees CCW, but we've lost the sign convention with the single mod, while the double mod seems to preserve the sign convention.

 

[IDS]

But the sign convention isn't lost with the single MOD, (and it is with the double MOD).

The angle we are calculating is positive for a clockwise rotation from the reference bearing to the heading, and the clockwise rotation is 2 degrees.
Anti-clockwise the rotation = -358 degrees.

Clockwise from -1 to +1 = 2
Anticlockwise from 359 to 1 = -358

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

How do you know if the answer from the single MOD version should be interpreted as clockwise or anticlockwise without doing something else?

 

[MintJulep]

@Doug,
Here, have fun!
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1686746450/tips/Relative_Bearing_hyp56p.xlsm[/url]

 

[IDS]

It's always positive so it is always clockwise. Just lookup the value in the table as shown in a previous post:

You can either use the 9 row table with the unmodified Heading-Bearing result (which will be between -360 and 360) or the 4 row table after applying the Mod function, where the result will be between 0 and 360.

Edit: See my first post on 15th June for revised version of formula using Sin+Cos, which gives consistent results.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Doug - please either include my observation my calc is off by 45 degrees or take it off the spreadsheet.

 

[MintJulep]

The OP's definitions of port and starboard are contrary to the conventional convention relative to the side of the ship.

https://en.m.wikipedia.org/wiki/Port_and_starboard

However there is the concept of "tiller command".

https://www.1066.co.nz/Mosaic DVD/stamford bridge/Tiller.htm

This likely causes some of our confusion.

 

[BridgeSmith]

Port and starboard will depend on what's relative to what, whether the goal is the ship's heading relative to the reference heading, or the reference heading relative to the ship's heading. Based on the terminology, the first one makes more sense.

 

[dik]

Starboard is the right side and port is the left side when facing forward, is that not correct? Same with aircraft, too?

-----*****-----
So strange to see the singularity approaching while the entire planet is rapidly turning into a hellscape. -John Coates

-Dik

 

[3DDave]

The question is - Turn to port to find the item or Item is towards the port side.

There is a training sailing ship that got smashed because the captain Yelled "turn to port" and the inexperienced crew on the tiller shoved the tiller to the port side causing a hard turn into the path of the other ship. There are too many collisions and Google isn't helpful.

 

[MintJulep]

https://en.wikipedia.org/wiki/Bearing_(angle)

 

[IDS]

It does seem the definition of port and starboard is causing some confusion, and the OP certainly isn't clear, but from the examples given it seems that the directions are based on the angle from the ship's direction of travel to the reference bore. If that isn't correct the Port and Stbd can be swapped in the tables or formulas. The problem with using the Abs function is that the result can change from Port to Stbd, or vice versa when the reference angle changes from 1 degree to 359 degrees, for instance.

3DDave - yes, your Sin+Cos formula will work if you use (Heading-Bore)+45, as shown below:

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

Updated to include a lookup for which way to look, relative to the vessel, to see the target.

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1686793335/tips/Relative_Bearing_by4cmo.xlsm[/url]

 

[IDS]

OK, so the double Mod doesn't have the same problem as using Abs, but it gives the same results as a single Mod, with the Port and Stbd swapped in the table.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

By the way, I don't see the need for the extra Mod, but I'm quite impressed with your short VBA code for drawing the compass.

Very neat


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

Thanks for your continued insistence Doug. The double MOD had been bothering me, because intuitively I didn't see the need for it either, and you and I typically converge on similar solutions.

I see now that changing cell B3 in my spreadsheet to =MOD(B2-B1,360) produces the same result.

That is MOD(Bearing_To_Target - Vessel_Heading, 360)

Somehow I either overlooked that (should have been) obvious solution, or had some other error that masked it as being correct.

The VBA is actually a simplification of something that I made many years ago to draw analog gauges. At the time I did it "just to see if I could". I was doing many other VBA animations for some training presentations.

It sat unused for probably 20+ years until a colleague needed an alternate to squiggly lines to show data recorder info to a client. He needed a very visual way to show "Look, this is what your operator is doing with the control. See how it makes the system pressure dance all over the place! That's bad."

 

[IDS]

Mintjulep - can I post your spreadsheet and code in a blog post?


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

MintJulep

I also would be interested to see your spreadsheet, but something on my computer, probably some form of anti-malware / anti-virus, insists on corrupting my download.[ ] Presumably to correct me.[ ] Probably objects to VBA.[ ] Turning off my anti-everything temporarily does not seem to help.

Can you post it again, but in a ZIP file with its extension changed to .ZZZ.[ ] That should sneak through.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician/analyst.[ ] See my profile for more details.[/sup]