Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet
Solution 1
Out of the box Excel has no support for what you are asking. There might be plugins around that help with this, but instead I'd like to offer a little work-around:
You can format numbers as time to get Excel (and probably OOo and LibreOffice) to display and calculate values that as you need with the correct divisions, bases, etc.
Using the following custom format (Excel, OOo and LibreOffice will have equivalents but I don't have a copy to hand to test) will render them with the correct symbols as well. The [h]
prevents the hour resetting to zero as it reaches 24 allowing arbitrarily large angles. If you'd like leading zero's then just double-up each letter.
[h]º m' s\"
These can be added together, etc, as necessary (they are just numbers underneath, after all). However, this will only work if the final result is positive; Excel will not will render negative times.
If you need to convert this value in to a decimal degrees value, you just need to multiply by 24, because a "day" in Excel time format has a value of 1 (so an hour is 1/24th, etc).
Just make sure the cell this formula goes in has normal formatting - not time:
=A1*24
Alternatively, you can also extract specific components from the time value by using the following:
=INT(A1*24) returns degrees
=MINUTE(A1) returns minutes
=SECOND(A1) returns seconds
Solution 2
Here are two macros that do the job for my astronomy spreadsheet. The direction values (deg, min, sec) are formatted as text. If cell A1 contains -77° 26' 09.5"
, I can enter =DMS2Real(A1)
in cell A2, which will then display -77.4359722222
. =Real2DMS(A2)
in A3 will reverse the conversion.
Enter the macro functions via Tools > Macros > Organize Macros > LibreOffice Basic > Standard (or wherever you would like to store them).
Note that decimal degrees are not the radian values required by trig functions.
' Convert degrees, minutes, seconds to a single real number and back.
' Tested in LibreOffice Calc 5.1.6.2
' Based on https://support.microsoft.com/en-gb/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle
Function DMS2Real(Degree_DMS As String) As Double ' -77° 26' 09.5" -> -77.4359722222
' use double precision floating-point.
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
Dim sign As Integer
' Set degree to value appearing before "°" in argument string.
degrees = Val(Left(Degree_DMS, InStr(1, Degree_DMS, "°") - 1))
' Only positive value is computed. Sign of result is changed in final statement
' i.e. -7° 8' 9" means (-7 - 8/60 - 9/3600)
If degrees < 0 Then
degrees = degrees * -1
sign = -1
Else
sign = 1
End If
' Set minutes to the value between the "°" and the "'"
' of the text string for the variable Degree_Deg divided by 60.
' The Val function converts the text string to a number.
minutes = Val(Mid(Degree_DMS, InStr(1, Degree_DMS, "°") + 2, InStr(1, Degree_DMS, "'") - InStr(1, Degree_DMS, "°") - 2)) / 60
' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = Val(Mid(Degree_DMS, InStr(1, Degree_DMS, "'") + 2, Len(Degree_DMS) - InStr(1, Degree_DMS, "'") - 2)) / 3600
DMS2Real = sign * (degrees + minutes + seconds)
End Function
Function Real2DMS(dms) ' -77.4359722222 -> -77° 26' 9.5"
DIM sign As String
' allow for negative values
If dms < 0 Then
dms = dms * -1
sign = "-"
Else
sign = " "
End If
'Set degree to Integer of Argument Passed
Degrees = Int(dms)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
Minutes = (dms - Degrees) * 60
'Set seconds to 60 times the number to the right of the
'decimal for the variable Minute
Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.0")
'Returns the Result of degree conversion
'(for example, 10.46 = 10~ 27 ' 36")
Real2DMS = sign & Degrees & "° " & Int(Minutes) & "' " & Seconds + Chr(34)
End Function
Sub Main
End Sub
Solution 3
There's no way to tell Excel to switch between radians and degrees when using functions like e.g.
=SIN(DEGREES)
so you would either have to do explicit conversions in each formula, e.g.
=SIN(RADIANS(DEGREES + MINUTES/60 + SECONDS/3600))
or you could create a custom formula in VBA like
Function SinDMS(Deg As Double, Min As Double, Sec As Double) As Double
SinDMS = Sin((3.1415 * 2 * (Deg + Min / 60 + Sec / 3600)) / 360)
End Function
(But it will be quite slow if there are many calculations)
Related videos on Youtube
Thalys
Updated on September 18, 2022Comments
-
Thalys over 1 year
My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle calculations in terms of DMS?
Edit: We're currently using excel 2007
Edit2: I suspect installing open office / libre office will be an option, and there may not be a MS Excel way of doing it - so i widening the question, Office 2007 is preferred but pretty much any common windows spreadsheet will do
Edit 3: Apparently using time as a workaround dosen't allow for multiplication- which is needed in this case
-
Thalys almost 13 yearsedited to add the version
-
Tog almost 13 yearsThis doesn't look hopeful, social.technet.microsoft.com/Forums/en-US/excel/thread/…
-
Thalys almost 13 yearsedited to add OOo or libre office as options, and that we need to be able to multiply the values in question
-
-
Thalys almost 13 yearsapparently i need them to multiply as well.
-
DMA57361 almost 13 yearsah, that's because each value isn't really itself, but 1/24th of itself, so
12º 0' 0"
is really being considered as0.5
by Excel. Try multiplying the final result of a multiplication (of two degrees values) by24
to correct for this. You shouldn't need to do this multiplying by a scaler. -
phuclv over 6 years3.1415 is far less accurate than 3.1416. Anyway it's better to use
WorksheetFunction.Pi
-
Community about 2 yearsYour answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.