Activity Feed › Discussion Forums › Software, CAD & Mapping › Help needed with angles in Excel

Help needed with angles in Excel
Posted by rfc on February 6, 2016 at 6:57 pmSome time ago, I inquired about how to convert Angles in ddmmmss to decimal degrees and back. Someone here helped out (still looking for the thread), and the bottom line is that I have a spreadsheet that does it:
t:
The formula is “=dms()” as shown in the image on the right.I’m trying to construct the formula for Azimuth by Solar Observation into a spreadsheet and before even getting to converting the decimal angles to radians, in order to use the trig functions, I tried first (in a new spreadsheet) to use “=dms()” and the sister formula “=ddeg()”, but my new spreadsheet shows no sign of those formulas.
I think whoever gave me the spreadsheet had made a custom formula to do this. I can’t find anywhere in the spreadsheet though where the custom formula is. Any Excel gurus out there have a clue here?
rfc replied 8 years, 2 months ago 7 Members · 15 Replies 
15 Replies

rfc, post: 356834, member: 8882 wrote: Some time ago, I inquired about how to convert Angles in ddmmmss to decimal degrees and back. Someone here helped out (still looking for the thread), and the bottom line is that I have a spreadsheet that does it:
t:
The formula is “=dms()” as shown in the image on the right.I’m trying to construct the formula for Azimuth by Solar Observation into a spreadsheet and before even getting to converting the decimal angles to radians, in order to use the trig functions, I tried first (in a new spreadsheet) to use “=dms()” and the sister formula “=ddeg()”, but my new spreadsheet shows no sign of those formulas.
I think whoever gave me the spreadsheet had made a custom formula to do this. I can’t find anywhere in the spreadsheet though where the custom formula is. Any Excel gurus out there have a clue here?
There are aa number of people who have writen custom functions (little scripts or programs) to do this and someone will undoubtedly chime in here about that. I chose to write mine using nothing but native functions. While it can become a nightmare, it sometimes makes them mor portable when going to portable variants of excel like pocket excel. That is probably not used much anymore, but it is why I got started on that route.
jlw

I just snooped around the “properties” pane in Excel, and it shows the author as Stephen Ward!! Bingo! He’ll probably know what the heck he did.:bored:

I am sure Stephen will when he sees it. He is a great guy and good friend. He has helped me many times. I hope I get the chance to return the favor some day.

May not be the best way, but, you can see how I did it in this thread https://surveyorconnect.com/threads/spiralcurvespreadsheet.321427/

you can find out how to use custom functions such as these here
http://www.wikihow.com/CreateaUserDefinedFunctioninMicrosoftExcel
and here
http://www.exceleasy.com/vba/examples/userdefinedfunction.htmlthe source code in the microsoft link in post#3 appears to do exactly what you want.
you already have the functions in a sheet by the look of it. so you just need to make them available to your new spreadsheet.
easy way is to “save as” the spreadsheet with the custom functions and then start working in that. you many need to save as xlsm or similar “macro enabled workbook”a better way might be to copy the functions into the “personal.xlsb” workbook. first record a dummy macro, choosing to record it in the personal workbook. then you can “unhide” the woorkbook. After that you will see it in the VBA editor (ALT+f11).

squowse, post: 356900, member: 7109 wrote: you can find out how to use custom functions such as these here
http://www.wikihow.com/CreateaUserDefinedFunctioninMicrosoftExcel
and here
http://www.exceleasy.com/vba/examples/userdefinedfunction.htmlthe source code in the microsoft link in post#3 appears to do exactly what you want.
you already have the functions in a sheet by the look of it. so you just need to make them available to your new spreadsheet.
easy way is to “save as” the spreadsheet with the custom functions and then start working in that. you many need to save as xlsm or similar “macro enabled workbook”a better way might be to copy the functions into the “personal.xlsb” workbook. first record a dummy macro, choosing to record it in the personal workbook. then you can “unhide” the woorkbook. After that you will see it in the VBA editor (ALT+f11).
I tried the (ALT+f11) thing, and Voila! There is the Visual Basic script Mr. Ward wrote. Not that I’m going to do much mucking around that far down in the engine room.o.O
I’m finding, in this endeavor, as is often the case, I just want to get into the car and drive to my destination (taking, and reducing efficiently, solar observations), rather than getting under the hood and reprogramming the Master Vehicle Control Unit!
I found though (as you point out), I can copy the spreadsheet into a new one, and the VB scripts come with it. If I can just easily manually enter the ephemeris data from MICA for Dec an LHA into the spreadsheet, I can use the script to get those to radians, and I should be good to go constructing the formula. Thanks all, and thanks Stephen for the original sheet.

Well, Here’s my first shot at the Solar Azimuth Calculator. The Data entered is: Lat and Long (from GE or Topo map), LHA and Dec from an ephemeris. From these, the formulae in A12 and B12 are derived. (I know I could have done it in a single long formula, but writing formulae in Excel is somewhat a pain in the….
If anyone sees any glaring errors, I’d love to know about them. 
Well, here’s my first attempt at a Solar Azimuth Calculator. Lat and Long are entered (from GE or a topo map); LHA and Dec are entered from an ephemeris.
The Formula for the Azimuth is in cell A12 and B12. I could have done it in one big formula, but entering formulae in Excel is a pain in the….I’ve checked it with my iPhone compass across the noon hour and it seems to be correct. If anyone sees any glaring errors, I’d love to know about them. 
I followed the instructions on the Microsoft page and it worked pretty easily. Thanks.

I’m making progress on further refinements of my Calculator, and found that I erred in accounting for the Semi Diameter of the Sun. See the instructions from the Sokkia manual:
From my sailing days, I used to just take the S.D. from the tables and subtract it (or add it) to the Upper or Lower Limb of the Sun. In my first pass at the spreadsheet, I just subtracted (or added) it to the computed Azimuth. But the Sokkia text seems to say the correction for the trailing edge sighting is a function of both the SD and the altitude. Is this to account for refraction due to the earth’s atmosphere (larger apparent SD at lower altitudes?), or is it just fine tuning the SD based on precise distance from the sun? 
Hello I have finally got around to making user defined functions for excel to convert DD to DMS and vice versa.
Would anyone like to do further testing? They are packaged as an Excel “addin” and are fairly simply installed. I have attached instructions for using it in Excel 2016. Older versions of excel may be different. You will have to google it.
The DMS formats I have chosen to use are
1) “packed” format used by Starnet and also very widely by other instruments and programs (eg inside the Leica XML file). This format gives 120.3955 for 39mins and 55secs. Further decimal places are decimal seconds.
2) Starnet angle format eg 1203955Reason being that I have some Starnet files where i need to convert “M” data lined to “DM” direction sets. So need to add/subtract bearings in DMS format.
I often wish that Star net would use decimal degrees (as well as DMS). But in this case I already have the DAT files converted from Leica DBX so this is what I am stuck with now.I haven’t done anything for what you call “Bearings” in the US. We don’t use that format at all.
The functions do automatically put the angle between 0 and 360 though.Addin can’t be uploaded her but you can download it from here https://www.sugarsync.com/pf/D3818606_05768639_6524680
Source code is below. I am not expecting any medals for efficient coding…'Functions to convert decimal degrees to DMS format and vice versa
'either starnet notation DDDMMSS.ssss, referred to in the function names as D_M_S
'or "packed" notation DDD.MMSSssss, referred to in the function names as DMS
'number of second decimal places can optionally be specified when converting to either DMS format
'DD to DMS conversion will automatically give an angle between 0 and 360 degrees
'This can also be invoked without conversion to DMS by using the correctDD function
'Function names are
'D_M_StoDD() takes a string as input)
'DMStoDD() takes a string as input
'DDtoD_M_S() takes a number as input
'DDtoDMS() takes a number as input
'correctDD takes a number as input
'written by Roger Walker 2016. no liability accepted for errors.
Function D_M_StoDD(dms_String As String)
Dim pOs As Integer
Dim D_string As String
Dim M_string As String
Dim s_sting As String
Dim D As Double
Dim M As Double
Dim S As Double
Dim dcircle As Double
Dim drange As Double
pOs = InStr(dms_String, "")
D_string = Left(dms_String, pOs  1)
ms_String = Mid(dms_String, pOs + 1)
pOs = InStr(ms_String, "")
M_string = Left(ms_String, pOs  1)
S_string = Mid(ms_String, pOs + 1)
D = CDbl(D_string)
M = CDbl(M_string)
S = CDbl(S_string)
D_M_StoDD = D + M / 60 + S / 3600
End Function
Function DMStoDD(dms_String As String)
Dim pOs As Integer
Dim sLen As Integer
Dim D_string As String
Dim M_string As String
Dim S_string As String
Dim D As Double
Dim M As Double
Dim S As Double
Dim dcircle As Double
Dim drange As Double
pOs = InStr(dms_String, ".")
D_string = Left(dms_String, pOs  1)
ms_String = Mid(dms_String, pOs + 1)
M_string = Left(ms_String, 2)
S_string = Mid(ms_String, 3)
sLen = Len(S_string)
If sLen < 2 Then sLen = 2
D = CDbl(D_string)
M = CDbl(M_string)
S = CDbl(S_string) / (10 ^ (sLen  2))
'correct degrees to between 0359
dcircle = D / 360
drange = Int(dcircle)
D = 360 * (dcircle  drange)
DMStoDD = D + (M / 60) + (S / 3600)
End Function
Function DDtoD_M_S(DD As Double, Optional secplaces As Integer = 2)
Dim D As Double
Dim MS As Double
Dim M As Double
Dim S As Double
Dim secFormat As String
Dim dcircle As Double
Dim drange As Double
Dim secString As String
If secplaces <= 0 Then
secFormat = "0"
Else
secFormat = "00."
For a = 1 To secplaces
secFormat = secFormat & "0"
Next a
End If
dcircle = DD / 360
drange = Int(dcircle)
DD = 360 * (dcircle  drange)
D = Int(DD)
MS = (DD  D) * 60
M = Int(MS)
S = (MS  M) * 60
secString = Format(CStr(S), secFormat)
If Left(secString, 2) = "60" Then
S = S  60
M = M + 1
End If
DDtoD_M_S = CStr(D) & "" & Format(CStr(M), "00") & "" & Format(CStr(S), secFormat)
End Function
Function DDtoDMS(DD As Double, Optional secplaces As Integer = 2)
Dim D As Double
Dim MS As Double
Dim M As Double
Dim S As Double
Dim secFormat As String
Dim dcircle As Double
Dim drange As Double
Dim secString As String
If secplaces <= 0 Then
secFormat = "0"
Else
secFormat = "00."
For a = 1 To secplaces
secFormat = secFormat & "0"
Next a
End If
dcircle = DD / 360
drange = Int(dcircle)
DD = 360 * (dcircle  drange)
D = Int(DD)
MS = (DD  D) * 60
M = Int(MS)
S = (MS  M) * 60
secString = Format(CStr(S), secFormat)
If Left(secString, 2) = "60" Then
S = S  60
M = M + 1
End If
DDtoDMS = CStr(D) & "." & Format(CStr(M), "00") & Replace(Format(CStr(S), secFormat), ".", "")
End Function
Function correctDD(DD As Double)
Dim dcircle As Double
Dim drange As Double
dcircle = DD / 360
drange = Int(dcircle)
correctDD = 360 * (dcircle  drange)
End Function

squowse, post: 373389, member: 7109 wrote: Hello I have finally got around to making user defined functions for excel to convert DD to DMS and vice versa.
Would anyone like to do further testing? They are packaged as an Excel “addin” and are fairly simply installed. I have attached instructions for using it in Excel 2016. Older versions of excel may be different. You will have to google it.
Way Cool, Sir! I’ll take ‘er for a spin!
Thank you.
Log in to reply.