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

 

[electricpete]

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.

 

[jhn1611]

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

 

[dik]

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

 

[IRstuff]

So, something like this?

=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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[davidbeach]

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

 

[jhn1611]

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

 

[jhn1611]

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

 

[MintJulep]

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.

 

[davidbeach]

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

 

[MintJulep]

"..just needs to cover the range from -360 to +360."

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

https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

 

[3DDave]

[pre]
A | B | C | D | E
1| Angle | Sin | Cos | Sin + Cos |
2| 0 | 0 | 1 | 1 | Fore[/pre]

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

 

[jhn1611]

Thanks 3DDave for taking the time

Forgive my ignorance and lack of intellect

Where do I input vessel heading and bore heading?

Thanks

 

[3DDave]

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

 

[jhn1611]

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

 

[Denial]

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.


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

 

[IDS]

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/

 

[3DDave]

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.
[pre]
=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",""))))[/pre]

 

[BridgeSmith]

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.

 

[BridgeSmith]

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.