stomped...converting bearings to angles

%3CLINGO-SUB%20id%3D%22lingo-sub-2279294%22%20slang%3D%22en-US%22%3Estomped...converting%20bearings%20to%20angles%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279294%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20can%20usually%20find%20a%20solution%20to%20what%20I%20am%20trying%20to%20do%20in%20excel%20but%20I%20am%20stomped%20on%20this%20one.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20hundreds%20of%20entries%20that%20I%20need%20to%20convert%20from%20surveyor%20bearings%20(like%20N%2037%3CSPAN%20class%3D%22ILfuVd%20NA6bn%22%3E%3CSPAN%20class%3D%22hgKElc%22%3E%C2%B0%3C%2FSPAN%3E%3C%2FSPAN%3E42'17%22%20W)%20to%20absolute%20decimal%26nbsp%3B%20angle%20.%20I%20have%20the%20mathematical%20conversion%20down%20path%20but%20I%20am%20stomped%20on%20how%20to%20automate%20the%20calculations.%3CBR%20%2F%3EThe%20sample%20bearing%20here%20is%20North%20by%20West%20meaning%20aiming%20North%20(0%2F360)%20and%20rotating%20towards%20the%20West%20(counter%20clockwise)%20so%20in%20this%20case%20the%20result%20should%20be%20360%20-%20the%20angle.%20If%20it%20were%20North%20by%20East%20then%20it%20should%20be%200%20%2B%20the%20angle.%3CBR%20%2F%3EUsing%20as%20a%20reference%20North%20as%200%2F360%20with%20a%20clockwise%20direction%20there%20are%204%20possibilities.%26nbsp%3B%3CBR%20%2F%3EN%20by%20W%20(360%20-angle)%3CBR%20%2F%3EN%20by%20E%20(0%2B%20angle)%3CBR%20%2F%3ES%20by%20W%20(180%20%2B%20angle)%3C%2FP%3E%3CP%3ES%20by%20E%20(180%20-%20angle)%3C%2FP%3E%3CP%3EHere%20are%20a%20few%20sample%20entries%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22483%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2269%22%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269%22%3Edegrees%3C%2FTD%3E%3CTD%20width%3D%2269%22%3Eminutes%3C%2FTD%3E%3CTD%20width%3D%2269%22%3Eseconds%3C%2FTD%3E%3CTD%20width%3D%2269%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269%22%3Edecimal%3C%2FTD%3E%3CTD%20width%3D%2269%22%3Eangle%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ES%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E37%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3E28.42694%3C%2FTD%3E%3CTD%3E208.4269%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ES%3C%2FTD%3E%3CTD%3E82%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E42%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3E82.21167%3C%2FTD%3E%3CTD%3E97.78833%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EN%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAs%20it%20is%20now%20I%20enter%20manually%20each%20formula%20to%20calculate%20the%20resulting%20angle.%3CBR%20%2F%3EI%20would%20like%20for%20the%20angle%20to%20calculate%20on%20its%20own%20based%20on%20the%20N-S-E-W%20directions.%3C%2FP%3E%3CP%3EAny%20suggestion%20on%20how%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2279294%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279338%22%20slang%3D%22en-US%22%3ERe%3A%20stomped...converting%20bearings%20to%20angles%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1031679%22%20target%3D%22_blank%22%3E%40PanamEricBM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMOD((A2%3D%22S%22)*180%2BIF(OR(A2%26amp%3BE2%3D%7B%22NW%22%2C%22SE%22%7D)%2C-1%2C1)*F2%2C360)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.