#1
|
|||
|
|||
the angle format (º ‘ ‘’) and calculation?
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#2
|
|||
|
|||
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 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") Code:
seconds = Application.Substitute(Right(Degree_Deg, Len(Degree_Deg) - _ Application.Find("'", Degree_Deg, 1)), """", "") / 3600 Code:
seconds = (Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + 1, _ Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600 |
#3
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Quote:
=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. |
#6
|
||||
|
||||
Quote:
BTW: Who uses centesimal angular measures?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#9
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#11
|
||||
|
||||
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:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Quote:
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, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#13
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#14
|
||||
|
||||
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)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
|
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 |