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

Jamal NUMAN · #1 04-21-2012, 01:59 PM

How to write the angle format (º ‘ ‘’) on the cell?

1. How to show the angle format in the cell, for example I need to type an angle like 120º12’23’’

2. How to do the calculation, for example 120º12’23’’ + 15º20’30’’

3. How to write “if statement” for example:

If c2>180º, then b2+15º20’30’’

If c2<180º, then b2-15º20’30’’

Thank you for the help

Best

Jamal

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

Jamal NUMAN · #3 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

macropod · #4 04-22-2012, 07:53 PM

Hi Jamal,

You can use a custom number format to display an angle in the ##º##'##'' format. However, this requires dividing an angle expressed in decimal degrees by 24 (eg =30/24 for a 30º angle). The custom number format (which is technically a time format) is:
[hh]ºmm'ss''
or, if you need even greater precision, you could use:
[hh]ºmm'ss.00''
Accordingly, depending on what you're trying to do at a certain point, you can use a formula like the one above or like =A1/24, plus the custom number format. For example, for a chart you might use the actuall values for drawing the angles and the custom formatted ones for the angle labels.

Catalin.B · #5 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 · #6 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 · #7 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 · #8 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

macropod · #9 04-23-2012, 02:26 PM

Hi Jamal,

As I said in my first post, the custom number format is:
[hh]ºmm'ss''
Simply copy the above and paste it into Excel's the custom number format box.

Jamal NUMAN · #10 04-23-2012, 03:28 PM

Quote:

Originally Posted by macropod

Hi Jamal,

As I said in my first post, the custom number format is:
[hh]ºmm'ss''
Simply copy the above and paste it into Excel's the custom number format box.

Many thanks Paul for your help and patience.

As usual, you are a star. My issue is solved now. The file and screenshot are attached.

I’m striving to proceed in calculations.

Best

Jamal

macropod · #11 04-23-2012, 04:19 PM

Hi Jamal,

For Sin(Ø) and Cos(Ø) you can use:
=SIN(RADIANS(C3)) and =COS(RADIANS(C3))
or:
=SIN(RADIANS(B3*24)) and =COS(RADIANS(B3*24))
in row 3 and copy down. This is more accurate than using your 3.14/180 conversion. For radians, you can use =RADIANS(C3) or =RADIANS(B3*24)

I'm not sure what you mean by:

Quote:

I’m striving to proceed in calculations.

In decimal terms, your angles add up to 540.0002778 degrees.

Jamal NUMAN · #12 04-24-2012, 12:49 PM

Quote:

Originally Posted by macropod

Hi Jamal,

For Sin(Ø) and Cos(Ø) you can use:
=SIN(RADIANS(C3)) and =COS(RADIANS(C3))
or:
=SIN(RADIANS(B3*24)) and =COS(RADIANS(B3*24))
in row 3 and copy down. This is more accurate than using your 3.14/180 conversion. For radians, you can use =RADIANS(C3) or =RADIANS(B3*24)

I'm not sure what you mean by:

In decimal terms, your angles add up to 540.0002778 degrees.

Many thanks Paul for the very crucial enhancement. It is much more powerful now.

I’m not sure if there is a way to remove the little minus sign (-) shown under the degree sign (º). Please, have a look on the attached screenshot.

I’m striving (motivated) to commence the entire angles calculation after your great help.

Best

Jamal

Jamal NUMAN · #13 04-24-2012, 02:06 PM

Quote:

Originally Posted by macropod

Hi Jamal,

For Sin(Ø) and Cos(Ø) you can use:
=SIN(RADIANS(C3)) and =COS(RADIANS(C3))
or:
=SIN(RADIANS(B3*24)) and =COS(RADIANS(B3*24))
in row 3 and copy down. This is more accurate than using your 3.14/180 conversion. For radians, you can use =RADIANS(C3) or =RADIANS(B3*24)

I'm not sure what you mean by:

In decimal terms, your angles add up to 540.0002778 degrees.

I’m again in trouble Paul.

I couldn’t know how to proceed with the “if statement” below:

If (B3>180º, B3-180º, B3+180º)

How the 180º in the “if statement” can be understood as angle in degrees?

The file and screenshot are attached

Best

Jamal

macropod · #14 04-24-2012, 03:06 PM

Hi Jamal,

The underscore under the degree symbol is part of the font you're using. If you don't like it, use a different font.

When using the custom format, all you need to remember is that values in it must be multiplied by 24 to get the 'real' values and 'real' values must be divided by 24 for the custom format. Of course, when comparing the custom format values to a 'real' value, you can apply the conversion to either the custom format value or the 'real' value.

Instead of your IF formula, you can use:
=MOD(B3,15)*24 or =MOD(B3*24,180)

Jamal NUMAN · #15 04-25-2012, 12:38 PM

Quote:

Originally Posted by macropod

Hi Jamal,

The underscore under the degree symbol is part of the font you're using. If you don't like it, use a different font.

When using the custom format, all you need to remember is that values in it must be multiplied by 24 to get the 'real' values and 'real' values must be divided by 24 for the custom format. Of course, when comparing the custom format values to a 'real' value, you can apply the conversion to either the custom format value or the 'real' value.

Instead of your IF formula, you can use:
=MOD(B3,15)*24 or =MOD(B3*24,180)

Many thanks Paul for the prompt answers

I couldn’t figure out how the MOD can replace the entire if statement

Then if statement says:

If C3> 180˚, then H3=C3-180˚

If C3<180˚, then H3=C3+180˚

Then how these two equations can be combined in one single MOD function?

I followed your valuable notes regarding multiplying and dividing by 24 and it works very well.

A screenshot and xlsx file are attached.

Best

Jamal

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