stomped...converting bearings to angles

Occasional Visitor



 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

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


In G2:




Fill down.