May not be a common problem but if you have 90.11020374 as DMS (i.e. 90?ø11'02.0374") in cell L2 in excel then =INT(L2)+TRUNC(MOD(L2,1)*100)/60+(MOD(L2,1)*100-TRUNC(MOD(L2,1)*100))*100/3600 seems to convert it to decimal degrees.
The controller software outputs it in the initial format and trying to do some calculations in excel hence the need to get it in decimal degrees so it can be converted to radians to use the trig functions in excel.
Try it with a negative angle.
I should preface the above with we only work in azimuth angles and vertical angles in our neck of the woods so I wouldn't have much need for that.
Plugging in a negative value does send things out to sea which I think is what you are inferring?
Do you have a better formula?
I should preface the above with we only work in azimuth angles and vertical angles in our neck of the woods
Just a point that needs clarifying, Are you using vertical angles or zenith angles??ÿ
I think that changing the first integer function to truncate will fix it. You never know when somebody might use that formula in another application that does encounter negative angles.
The following formula would be so much shorter with some intermediate cells serving as surrogates but I believe this formula would handle negative values and second values of varying lengths (I did not test in situations where only dd.mm are provided. Again, source dd.mmsssss value in cell L2 is assumed):
=TRUNC(L2) & RIGHT(MID(L2,SEARCH(".",L2)+1,2)/60+(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2)/10^(LEN(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2))-2))/3600,LEN(MID(L2,SEARCH(".",L2)+1,2)/60+(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2)/10^(LEN(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2))-2))/3600)-1)
I hope this information helps.
yes fair point, not a big deal for me but would be important for those trying to use it with lat/longs or quadrant bearings
another good option, was trying to avoid using the text functions in excel. If I came up with more significant figures for the seconds I could just adjust the final *100/3600 to suit *1000/36000 etc.
zenith angles are what the TS is storing, then I use =ABS(90-L2) to convert to vertical angles so I can do the slope distance reduction to horizontal distance/vertical distance
OK, how about this modification then, which uses the length of L2 to govern the length of the result and converts the the text string result into its numerical value?
=VALUE(LEFT(TRUNC(L2) & RIGHT(MID(L2,SEARCH(".",L2)+1,2)/60+(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2)/10^(LEN(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2))-2))/3600,LEN(MID(L2,SEARCH(".",L2)+1,2)/60+(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2)/10^(LEN(RIGHT(L2,LEN(L2)-SEARCH(".",L2)-2))-2))/3600)-1),LEN(L2)))
Tweak to tastes... 🙂
hence the reason I use grads (aka gons, where 2PI=400g, 0.9g=1?ø) rather than DMS in all my comps.
That's a knotty little problem if you want to create a completely general formula. Modulus arithmetic won't handle negative angles and calculations in floating point arithmetic sometimes frustrate the truncate function by leaving numbers just tiny bit less than the whole number they represent.
The one below covers a lot of bases. It handles negative angles, zero or blank seconds, and any reasonable number of decimal places in seconds. But it relies on an ancient trick from olden programming days, namely adding a tiny amount to a floating point number to bump it over an integer hump, and a test for positive or negative input.
The math is straight forward, the bugs remain to be found. Give it a try.
=if(L2>0,trunc(L2)+trunc(100*(L2+1E-14-trunc(L2)))/60+100*(100*L2-trunc(100*L2))/3600,trunc(L2)+trunc(100*(L2-1E-14-trunc(L2)))/60+100*(100*L2-trunc(100*L2))/3600)