stomped...converting bearings to angles

Copper Contributor

Hello,

 

 I can usually find a solution to what I am trying to do in excel but I am stomped on this one.

I have hundreds of entries that I need to convert from surveyor bearings (like N 37°42'17" W) to absolute decimal  angle . I have the mathematical conversion down path but I am stomped on how to automate the calculations.
The sample bearing here is North by West meaning aiming North (0/360) and rotating towards the West (counter clockwise) so in this case the result should be 360 - the angle. If it were North by East then it should be 0 + the angle.
Using as a reference North as 0/360 with a clockwise direction there are 4 possibilities. 
N by W (360 -angle)
N by E (0+ angle)
S by W (180 + angle)

S by E (180 - angle)

Here are a few sample entries

 degreesminutesseconds decimalangle
S282537W28.42694208.4269
S821242E82.2116797.78833
N4500E4545

As it is now I enter manually each formula to calculate the resulting angle.
I would like for the angle to calculate on its own based on the N-S-E-W directions.

Any suggestion on how to do this?

1 Reply

@PanamEricBM 

In G2:

 

=MOD((A2="S")*180+IF(OR(A2&E2={"NW","SE"}),-1,1)*F2,360)

 

Fill down.