×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Compass question excel
4

Compass question excel

Compass question excel

(OP)
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


RE: Compass question excel

It is trivial to convert a numerical value in degrees to one of 4 values depending on the input. You might use nested if's, or else a case statement
https://www.statology.org/case-statement-in-excel/

I'm not sure you have told us enough about your inputs for us to be able to write an equation for you, if that's what you're looking for. I don't know the range of your inputs or what they represent.

RE: Compass question excel

(OP)
Hi electricpete, thanks for the reply.

My excel knowledge is average to basic so appreciate your help

So regarding the inputs I will provide some examples

Currently we are working at location where the 'Reference bore (RB)' is 3deg. The vessel is on 180 deg heading. Hence, RB is AFT in relation to the vessel.

In the next few we will have a vessel heading change to 270 deg which will make the RB STBD

On our next location the the RB is 190 deg. On our current vessel; heading of 180 the RB would be FWD

Any more info / expamples i can provide please let me know

Thanks

RE: Compass question excel

great link electri...

The root of the guide is

https://www.statology.org/excel-guides/

and has a lot of other information.

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

-Dik

RE: Compass question excel

I don't think you can use ABS(VC-RBC) because that just reflects about 0. I think you need to calculate VC-RBC and then add 360 to it if it comes out less than 0.

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

RE: Compass question excel

(OP)
Thanks IRStuff for taking the time to post. Its nearly there...

As davidbeach suggested it doesn't quite work.

When we have a VC heading close to 360 and bore heading slightly greater than zero. For example VC 350 AND RBC 5 calculates AFT but should be FWD

Thanks

RE: Compass question excel

(OP)
I believe this is where MOD function could be implemented???

For example =MIN(MOD(VC-RBC,360), 360-MOD(VC-RBC,360))

This calculates a difference of 15 when VC 350 and RBC 5.

15 is =<45 and would correctly output FWD

I just dont know how to combine it...

Thanks

RE: Compass question excel

The MIN() in the reference earlier formula removes the directionality from the result, whereas in your case you need to maintain directionality.

I would be inclined to use a table and lookup instead of a nested if. Easier to understand.

RE: Compass question excel

Table and lookup works, just needs to cover the range from -360 to +360.

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

RE: Compass question excel

    A    |  B  |  C  |     D     |  E
1| Angle | Sin | Cos | Sin + Cos |
2|   0   |  0  |  1  |     1     |  Fore 

E2 =IF(D2>=1,"Fore",IF(D2<=-1,"Aft",IF(AND(D2<1,D2>-1,C2>B2),"Port",IF(AND(D2<1,D2>-1,C2<B2),"Stbd",""))))

(Reverse Port and Stbd if I got theme backwards.)

RE: Compass question excel

(OP)
Thanks 3DDave for taking the time

Forgive my ignorance and lack of intellect

Where do I input vessel heading and bore heading?

Thanks

RE: Compass question excel

Wherever you like; the angle is the difference between the two.

RE: Compass question excel

(OP)
Hi 3DDave

Sorry I cannot get it to work.

Cab you explain in basic terms how to write the formula below so it calculates the angle

A | B | C | D | E
1| Angle | Sin | Cos | Sin + Cos |
2| 0 | 0 | 1 | 1 |

Thanks again

RE: Compass question excel

A surprisingly complicated little problem.

Here is a single formula that avoids a tangle of nested IF() statements and ElectricPete's excellent SWITCH() suggestion (which is only available in more recent versions of Excel).

=INDEX({"FWD","STBD","AFT","PORT"},ROUNDDOWN(MOD(("DatumBore"-"Vessel"+360+45)/90,4)+1,0))

However, simple it ain't.

 —————————————————————————————————
Engineering mathematician/analyst.  See my profile for more details.

RE: Compass question excel

Edited: The minimum value of the lookup table must be <= the minimum lookup value. I have added a row to the top of the table, and edited the formula.

I'm with MintJulep on this one.

The screenshot below shows a spreadsheet using a lookup (Cell A5), the 3DDave formula (A12) and the Denial formula (A17).

The Lookup and Denial's formula give the same results, but the lookup is much easier to check and to see how it works.

The formula with Sin and Cos gives different results in some cases, but working out why looking at the formula is not easy to see why.

Also note that:
The Excel trig functions use radians. Using degrees will give wrong answers but again it won't be obvious why.
I have given the cells A2 and B2 the names Ref_Bore and Heading, and used these in Denial's formula. If using range names in a formula don't add "".



I have also attached the spreadsheet below.


Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Compass question excel

Ha - I'm off by 45 degree error.

Graphing the sine (blue), cos (red), sine+cos (green) to see the way it was supposed to work.


Lookup means it cannot be a single cell solution.
=IF(D2>=1,"Fore",
IF(D2<=-1,"Aft",
IF(AND(D2<1,D2>-1,C2>B2),"Port",
IF(AND(D2<1,D2>-1,C2<B2),"Stbd","")))) 

RE: Compass question excel

Quote:

The Excel trig functions use radians. Using degrees will give wrong answers but again it won't be obvious why.
Degrees can be converted to radians using "RADIANS(angle in degrees)"

Nevermind. I see that you've used it in the spreadsheet you attached.

RE: Compass question excel

Would it be as simple as the absolute value of reference bore - heading?

I opened IDS' Excel sheet, changed cell C2 to read "=ABS(B2-A2)".
Then wrote in another cell "=IF(C2>45,IF(C2<135,"PORT",IF(C2<225,"AFT",IF(C2<315,"STBD","FORE"))),"FORE")"

I may have the Port and starboard mixed up, since I'm not sure whether it's supposed to give bore relative to heading or heading relative to bore, but if they're reversed, then you can just substitute in the formula.

RE: Compass question excel

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/

RE: Compass question excel

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

RE: Compass question excel

Quote:

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.

RE: Compass question excel

(OP)
Thanks to all for the help

Your one seems to work perfect BridgeSmith

RE: Compass question excel

Quote (BridgeSmith)

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/

RE: Compass question excel

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.

RE: Compass question excel

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.

RE: Compass question excel

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

RE: Compass question excel

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.

RE: Compass question excel

Quote (BridgeSmith)

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/

RE: Compass question excel

Quote:

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.

RE: Compass question excel

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)

RE: Compass question excel

Quote:

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/

RE: Compass question excel

The MOD version is much cleaner.

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

RE: Compass question excel

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.

RE: Compass question excel

Hmmm, nope.

RE: Compass question excel

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

RE: Compass question excel

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/

RE: Compass question excel

Try it with small headings on either side of zero.

RE: Compass question excel

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/

RE: Compass question excel

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

RE: Compass question excel

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Compass question excel

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.





RE: Compass question excel

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/

RE: Compass question excel

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

RE: Compass question excel

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/

RE: Compass question excel

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

RE: Compass question excel

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.

RE: Compass question excel

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

RE: Compass question excel

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.

RE: Compass question excel

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/

RE: Compass question excel

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/

RE: Compass question excel

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/

RE: Compass question excel

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."

RE: Compass question excel

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Compass question excel

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.

 —————————————————————————————————
Engineering mathematician/analyst.  See my profile for more details.

RE: Compass question excel

Here's the entire VBA code.

CODE --> VBA

Coming soon to IDS's blog at
http://newtonexcelbach.wordpress.com/
A more robust version with comments. 

RE: Compass question excel

Quote (MintJulep)

@Doug, check your email.

Just found that gmail rejected my e-mail and Outlook sent gmail's notification to junk, whilst letting through stuff from random spammers asking me to sign something!

Just tried again from my gmail account.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Compass question excel

I have now posted a summary of the proposed formulas at:
https://newtonexcelbach.com/2023/06/19/calculating...

The link has a download which includes both my spreadsheet and MintJulep's, which now has updated VBA code. Please let me know if any problems downloading.

Note that I have modified the tables and formulas to use (Reference Bearing - Ship Heading) as the angle, so that positive values are clockwise.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close