Notifications
Clear all

Help needed with angles in Excel

16 Posts
7 Users
0 Reactions
77 Views
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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?

 
Posted : February 6, 2016 11:57 am
(@jlwahl)
Posts: 204
Member
 

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

 
Posted : February 6, 2016 12:14 pm
(@bobkrohn)
Posts: 158
Member
 

saw this
https://support.microsoft.com/en-us/kb/213449

 
Posted : February 6, 2016 12:29 pm
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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:

 
Posted : February 6, 2016 12:46 pm
jimmy-cleveland
(@jimmy-cleveland)
Posts: 2812
Member
 

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.

 
Posted : February 6, 2016 7:37 pm

norm-larson
(@norm-larson)
Posts: 986
Supporter
 

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

 
Posted : February 6, 2016 8:04 pm
(@squowse)
Posts: 1004
Member
 

you can find out how to use custom functions such as these here
http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel
and here
http://www.excel-easy.com/vba/examples/user-defined-function.html

the 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).

https://support.office.com/en-us/article/Copy-your-macros-to-a-Personal-Macro-Workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

 
Posted : February 7, 2016 4:11 am
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

squowse, post: 356900, member: 7109 wrote: you can find out how to use custom functions such as these here
http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel
and here
http://www.excel-easy.com/vba/examples/user-defined-function.html

the 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).

https://support.office.com/en-us/article/Copy-your-macros-to-a-Personal-Macro-Workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

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.

 
Posted : February 7, 2016 6:22 am
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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.

Attached files

Solar Azimuth Calculator.xls (45.5 KB) 

 
Posted : February 7, 2016 1:41 pm
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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.

Attached files

Solar Azimuth Calculator.xls (45.5 KB) 

 
Posted : February 7, 2016 1:57 pm

(@dan-patterson)
Posts: 1272
Member
 

I followed the instructions on the Microsoft page and it worked pretty easily. Thanks.

 
Posted : February 9, 2016 2:34 pm
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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?

 
Posted : February 9, 2016 6:42 pm
 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

Here's my updated Solar Azimuth Calculator sheet.

Attached files

Solar Azimuth Calculator v2.xls (48 KB) 

 
Posted : February 10, 2016 2:36 pm
(@squowse)
Posts: 1004
Member
 

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 "add-in" 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 120-39-55

Reason 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.

Add-in 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 DDD-MM-SS.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 0-359
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
Attached files

Instructions for using DD to DMS functions add.docx (303 KB) 

 
Posted : May 22, 2016 12:16 pm
(@squowse)
Posts: 1004
Member
 

updated source code here https://www.sugarsync.com/pf/D3818606_07715358_698255

 
Posted : May 22, 2016 2:19 pm

 rfc
(@rfc)
Posts: 1901
Member
Topic starter
 

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 "add-in" 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.

 
Posted : May 22, 2016 6:03 pm