## 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

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

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

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

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

=IF(ABS(VC-RBC)<=45,"FWD", IF(ABS(VC-RBC)>=135,"AFT",IF(VC<RBC,"STBD","PORT")))

TTFN (ta ta for now)

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

## RE: Compass question excel

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

## RE: Compass question excel

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

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

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

## RE: Compass question excel

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

## RE: Compass question excel

Yes, in 45 degree increments. So the table only needs eight entries.

https://support.microsoft.com/en-us/office/xlookup...

## RE: Compass question excel

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

Forgive my ignorance and lack of intellect

Where do I input vessel heading and bore heading?

Thanks

## RE: Compass question excel

## RE: Compass question excel

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

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

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

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.

## RE: Compass question excel

~~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

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

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

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

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

Your one seems to work perfect BridgeSmith

## RE: Compass question excel

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

## RE: Compass question excel

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

-----*****-----

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

-Dik

## RE: Compass question excel

## RE: Compass question excel

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

~~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

## RE: Compass question excel

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

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

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

## RE: Compass question excel

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

## RE: Compass question excel

=MOD(360-MOD(Vessel_Heading - True_Bearing_To_Target,360),360)

## RE: Compass question excel

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

## RE: Compass question excel

Can you give an example that doesn't work?

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

## RE: Compass question excel

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

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

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

## RE: Compass question excel

Here, have fun!

https://res.cloudinary.com/engineering-com/raw/upload/v1686746450/tips/Relative_Bearing_hyp56p.xlsm

## RE: Compass question excel

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

## RE: Compass question excel

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

However there is the concept of "tiller command".

https://www.1066.co.nz/Mosaic%20DVD/stamford%20bri...

This likely causes some of our confusion.

## RE: Compass question excel

## RE: Compass question excel

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

-Dik

## RE: Compass question excel

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

## RE: Compass question excel

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

https://res.cloudinary.com/engineering-com/raw/upload/v1686793335/tips/Relative_Bearing_by4cmo.xlsm

## RE: Compass question excel

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

Very neat :)

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

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

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Compass question excel

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

## CODE --> VBA

## RE: Compass question excel

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

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/