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

Catalin.B · #1 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...

Jamal NUMAN · #2 04-22-2012, 01:29 PM

Quote:

Originally Posted by Catalin.B

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...

Thank you for the very informative answer.

Actually, I couldn’t know how to use the function that you have supplied. How do I use it to convert the decimal angles to DMS? Could you please elaborate more?

I think that the second part of my issue is still not solved. What if I need to insert the 35º12’14’’ directly to the cell? Do I need in this case to convert it first to decimal and then using the code to convert it to DMS?

Best

Jamal

Catalin.B · #3 04-23-2012, 03:13 AM

Quote:

Originally Posted by Jamal NUMAN

How do I use it to convert the decimal angles to DMS? Could you please elaborate more?

I think that the second part of my issue is still not solved. What if I need to insert the 35º12’14’’ directly to the cell? Do I need in this case to convert it first to decimal and then using the code to convert it to DMS?

Best

Jamal

Copy those functions in a VB module. Then, to convert to degree, in cell A3 of your worksheet use the formula: =Convert_Degree(A1) to convert a decimal value from cell A1 to angular value, displayed as DMS. This is now, a text string in A3, not a value! So if you need to 2 cells that looks like 15°19'55,2" you have to convert these text strings back to a decimal value, add them, then convert them to a sexagesimal value, to be displayed in DMS. Assuming that you have 19°22'37,2" in A1 and 15°19'55,2" in A2, the formula to sum A1+A2 in A3 should look like:
=Convert_Degree(Convert_Decimal(A1)+Convert_Decima l(A2))
You can enter a value in DMS as a text string, which will be transformed in calculations in decimal.
If in A1 is a decimal value, and in A2 a value inserted by you, like 35º12’14’’, then use in A3 the functions like this(convert A2 to decimal, add it to A1, then convert the result to be displayed as DMS):
=Convert_Degree(A1+Convert_Decimal(A2))
But, you have another option from Paul, which is a great answer, i am ashamed that i did not realised that sexagesimal circle matches perfectly the time circle and DMS can be displayed by formatting the decimal value to a custom time format [hh]ºmm'ss.00''. With this format, the results can be simply added and the cell formatted to display DMS
As Paul sugested, use (A1+A2)/24 in a custom formatted cell and you will have the same answer as the UDF. The problem with this approach is that you cannot enter a value in DMS, values can only be entered in decimal system. And another minus is that this can be applied only to sexagesimal system, will not work on centesimal system (at least i think so...

)To work on centesimal system , the functions are easy to modify, replacing 60 with 100 and 3600 with 10000.

macropod · #4 04-23-2012, 04:40 AM

Quote:

Originally Posted by Catalin.B

As Paul sugested, use (A1+A2)/24 in a custom formatted cell and you will have the same answer as the UDF. The problem with this approach is that you cannot enter a value in DMS, values can only be entered in decimal system.

Actually, you can - simply input the DMS in HH:MM:SS format into a cell formatted with my custom number format. For example, for an angle of 10º15'22'', input 10:15:22. To get the decimal equivalent of that in another cell, simply multiply it by 24.

BTW: Who uses centesimal angular measures?

Catalin.B · #5 04-23-2012, 04:45 AM

Quote:

Actually, you can - simply input the DMS in HH:MM:SS format into a cell formatted with my custom number format. For example, for an angle of 10º15'22'', input 10:15:22. To get the decimal equivalent of that in another cell, simply multiply it by 24.

And again i bite the dust... :d

Jamal NUMAN · #6 04-23-2012, 01:45 PM

Quote:

Originally Posted by macropod

Actually, you can - simply input the DMS in HH:MM:SS format into a cell formatted with my custom number format. For example, for an angle of 10º15'22'', input 10:15:22. To get the decimal equivalent of that in another cell, simply multiply it by 24.

BTW: Who uses centesimal angular measures?

Thank you guys for the help. This is very useful.

I’m wondering why the Excel doesn’t have such data format as built in feature.

We use complicated computations in the engineering surveying that includes angles as a main data. All the computations are based on the angles in DMS.

For example, as we collect angle data from the fields, the sum of the measured angles for any polygon should equal (n-2)*180. If the sum is different from this value, then the error is calculated and then distributed in all the measured angles. This is why a lot of angle computation is involved (screenshot).

I couldn’t find the ##º##’##.##’’ on the custom (the screenshot is attached) so that I can make the 10:15:22 appears as 10º15’22’’

How can i enter all the angle values (shown in screenshot one) so that they appear in DMS format and can be processed applying particular calculations.

Best

Jamal

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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