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?
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/spiral-curve-spreadsheet.321427/
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).
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.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?
Here's my updated Solar Azimuth Calculator sheet.
updated source code here https://www.sugarsync.com/pf/D3818606_07715358_698255
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.
