the angle format (º ‘ ‘’) and calculation?

Catalin.B · #2 04-22-2012, 12:22 AM

It's not easy...
Let's start: assuming that in cell A1 we have : 15,332, the value of an angle formatted as decimal.
To convert this in DMS, we can do this by converting this value:
to find degrees:
=INT($A$1)
to find minutes:
=INT(($A$1-INT($A$1))*60)
to find seconds:
=(($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60
We can combine this results to show a piece of text formatted as DMS:
=INT($A$1)&"°"&INT(($A$1-INT($A$1))*60)&"'"&ROUND((($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60;2)&""""
To convert back this result 15°19'55,2" back to decimal value, this is another story...
to convert degrees:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))
to convert minutes:
=--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60
to convert seconds:
=--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600
Then, if you sum this 3 results, you will have the starting value in decimal value: 15,332:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))+--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60+--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600

You can use microsoft UDF, to do this:

Code:

Function Convert_Degree(Decimal_Deg) As Variant
    With Application
        'Set degree to Integer of Argument Passed
        degrees = Int(Decimal_Deg)
        'Set minutes to 60 times the number to the right
        'of the decimal for the variable Decimal_Deg
        minutes = (Decimal_Deg - 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")
        'Returns the Result of degree conversion
       '(for example, 10.46 = 10~ 27  ' 36")
        Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _
            & seconds + Chr(34)
    End With
End Function

Function Convert_Decimal(Degree_Deg As String) As Double
   ' Declare the variables to be double precision floating-point.
   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   ' Set degree to value before "°" of Argument Passed.
   degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
   ' 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_Deg, InStr(1, Degree_Deg, "°") + 2, _
             InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
             "°") - 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_Deg, InStr(1, Degree_Deg, "'") + _
            2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
            / 3600
   Convert_Decimal = degrees + minutes + seconds
End Function

but you shoud know that it has an error, the function will return 15°19'55" instead of 15°19'55,2", which is the precise conversion, and the reverse process is 15,33194 instead of 15,332
To solve this error in UDF, i would change the seconds determination in Convert_Degree to:

Code:

seconds = Format(((minutes - Int(minutes)) * 60), "#,#0.0")

and in Convert_Decimal:

Code:

seconds = Application.Substitute(Right(Degree_Deg, Len(Degree_Deg) - _
    Application.Find("'", Degree_Deg, 1)), """", "") / 3600

or:

Code:

seconds = (Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + 1, _
    Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600

With all this in mind (

), you can play with your needs...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date Calculation	Lights	Excel	5	04-18-2012 04:31 AM
calculation of start date	ketanco	Project	1	02-29-2012 07:01 AM
Formula calculation	danbl	Excel	8	02-23-2012 04:35 AM
Calculation within Cells	manich1	Excel	2	12-07-2011 02:59 PM
Angle Bracket Problem, STYLEREF in Header of a Protected Document	wordjunkie	Word	0	06-18-2010 04:34 AM