5

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

2

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
  • apparently i need them to multiply as well. – Journeyman Geek May 11 '11 at 8:46
  • ah, that's because each value isn't really itself, but 1/24th of itself, so 12º 0' 0" is really being considered as 0.5 by Excel. Try multiplying the final result of a multiplication (of two degrees values) by 24 to correct for this. You shouldn't need to do this multiplying by a scaler. – DMA57361 May 11 '11 at 8:50
1

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
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.