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

 

[IDS]

But Abs will give the same result for +90 and -90, which isn't right.

You could use Mod to reduce the length of the table though:

If you must, you could incorporate the Mod in a function without using a table, which is what denial did.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

IDS... your last approach is the most elegant and clear. Actually denials is the most elegant... my apologies.

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

-Dik

 

[BridgeSmith]

But Abs will give the same result for +90 and -90, which isn't right.

As I understand it, it's only necessary to have the angle between the reference and the ship's heading, which is always a positive between zero and 360 degrees. Port would be an angle from 45 and 135 degrees and starboard would be from 225 to 315 degrees.

 

[jhn1611]

Thanks to all for the help

Your one seems to work perfect BridgeSmith

 

[IDS]

As I understand it, it's only necessary to have the angle between the reference and the ship's heading, which is always a positive between zero and 360 degrees. Port would be an angle from 45 and 135 degrees and starboard would be from 225 to 315 degrees.

For instance, if Bore = 350 and Heading = 40 then head-bore = -310, which is Port, but 310 is FWD/Fore
Edit: 310 gives STBD

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BridgeSmith]

IDS, the angle between the two will always be a positive number, moving clockwise from one to the other. I just wasn't sure what to subtract from what to have it be correct for Port and Starboard.

 

[3DDave]

From one to the other is fine as long as they are interchangeable.

However, the actual ship heading and the reference heading are not interchangeable.

If the ship heading is 0 and the reference is any number then (ship heading) - (reference heading) is negative unless the reference heading is also 0.

 

[dik]

Is that sort of like airspeed and groundspeed with aircraft?

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

-Dik

 

[BridgeSmith]

3DDave, they are not interchangeable, but a heading relative to any reference direction is always a positive number of degrees between 0 and 360, measured clockwise from the reference vector.

 

[IDS]

they are not interchangeable, but a heading relative to any reference direction is always a positive number of degrees between 0 and 360, measured clockwise from the reference vector.

But the OP asks for the difference between two headings, which will not always be positive. If the difference is negative you need to add 360, not change the sign.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BridgeSmith]

But the OP asks for the difference between two headings, which will not always be positive.

Yes, it is always positive - it's measured moving clockwise from the reference to the heading. There is no such thing as a -90 heading; 90 degrees counterclockwise is 270 degrees.

Oops, I see what you're saying now. I may have to rethink this. It doesn't work when the 360/zero mark is between the reference and the heading.

 

[BridgeSmith]

Ok, I had to think about it for a minute, and I think I have a solution that will get the angle correctly. To get the angle from reference(R) to heading (H), I think it requires an if statement like this --> IF(R>H,360-R+H,H-R)

 

[IDS]

IF(R>H,360-R+H,H-R)

... Which gives the same result as =MOD(H-R, 360)



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[davidbeach]

The MOD version is much cleaner.

I’ll see your silver lining and raise you two black clouds. - Protection Operations

 

[MintJulep]

So this boils down to a frame of reference transformation.

Vessel heading and true bearing to target are both Earth frame of reference. Fore, Aft, Starboard, Port are vessel frame of reference (relative bearing to target (reduced to four cardinal directions)).

Vessel heading 0
True Bearing to target 90
90 - 0 = 90 Target is 90 degrees clockwise from the vessel's bow, i.e. to the vessel's Starboard side.

Vessel heading 180
True Bearing to target 90
90 - 180 = -90 Target is 90 degrees CCW (270 degrees CW) from the vessel's bow, i.e. to the vessel's Port side.

Vessel heading 270
True Bearing to target 90
90 - 270 = -180 Target is 180 degrees (CW or CCW) from the bow, i.e. to the vessel's aft.

Etc.

To get this sign convention: MOD(Vessel_Heading - True_Bearing_To_Target,360)

From there, I still vote for a lookup.

 

[MintJulep]

Hmmm, nope.

 

[MintJulep]

Ok, I think this is it.
=MOD(360-MOD(Vessel_Heading - True_Bearing_To_Target,360),360)

 

[IDS]

Why the double MOD?

MOD(Vessel_Heading - True_Bearing_To_Target,360) with a Lookup seems to work fine to me.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

Try it with small headings on either side of zero.

 

[IDS]

359.9999 and 0.0001 works OK.

Can you give an example that doesn't work?


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/