6

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

3

4 Answers 4

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
2
  • apparently i need them to multiply as well.
    – Journeyman Geek
    May 11, 2011 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, 2011 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)

1
0

Perform the math in degrees within Excel and then convert the answer to degrees, minutes and seconds using the formula (setting the input angle in cell A3):

=+FLOOR(A3,1)&" deg "&FLOOR((A3-(FLOOR(A3,1)))*60,1)&" min "&ROUND((((A3-(FLOOR(A3,1)))*60)-FLOOR((A3-(FLOOR(A3,1)))*60,1))*60,0)&" sec"
1
  • 1
    Your 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.
    – Community Bot
    Apr 12, 2022 at 14:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .