DD to DMS conversion without rounding.

[Skylar289]

I stole this from IML on June 25, 2001 11:43 AM tried to PM but I'm new so please don't harp on me too much please!




Assuming your number is A1, try the following
=INT(A1)&CHAR(186)&" "&INT((A1-INT(A1))*60)&CHAR(145)&" "&ROUND((((+A1-INT(A1))*60)-INT((+A1-INT(A1))*60)),2)*60&CHAR(145)


The calculation works fine, but rounds... I even tried adjusting &ROUND() but for some reason it isn't working


dd 34.616645 -112.4532 should come out to

DMS 34 36 59.9 N 112 27 11.5 W



The above formula rounds to 34 36 60 N 112 27 12


In my case, this is a 40 ft difference that I can not accept. I could write multiple cells that combine separate cells to get my equation by doing basic math, but just looking for a PRO solution from your community.

 

[shg]

How about ...

=IF(A1<0, "-","") & TEXT(ABS(A1/24), "[h]° mm' ss.0''")

 

[Skylar289]

Solvedddd

Just got yelled at by my professor.

A27 being the DD

=INT(A27)&CHAR(186)&" "&INT((A27-INT(A27))*60)&CHAR(145)&" "&ROUND((((+A27-INT(A27))*60)-INT((+A27-INT(A27))*60)),10)*60&CHAR(145)

This puts you within 0 meters. Thanks!

 

[shg]

She's going to yell some more if you use that formula.

Try a negative angle.

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=310><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 4132" width=90><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 4352" width=95><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 5046" width=110><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 68pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=16 width=90>Input</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 11pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=15> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=95>Output 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=110>Output 2</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>164.961908</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>164° 57' 42.87''</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>164º 57‘ 42.8688‘</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>-7.996738</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>-7° 59' 48.26''</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>-8º 0‘ 11.7432‘</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>138.655921</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>138° 39' 21.32''</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>138º 39‘ 21.3156‘</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>72.011654</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>72° 00' 41.95''</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33>72º 0‘ 41.9544‘</TD></TR></TBODY></TABLE>

 

[jasonb75]

Does the suggestion in post #2 give high enough accuracy?

You can increase the accuracy slightly if needed (see B27 below), beyond that you would need a longer formula like your original (see C27 below), I've revised this formula to give correct results.

I'm sure there is a way to combine the 2 methods and create a shorter formula with higher accuracy, but I can't see it, maybe shg or someone else can.

Excel Workbook
	A	B	C
27	-112.4532	-112 27' 11.520"	-112 27 11.519999994
28	34.616645	34 36' 59.922"	34 36 59.921999994
Sheet3

 

[shg]

Arc seconds with three decimals is a precision of about 3cm, greater than the 11cm precision of degrees with 6 decimals ...