• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rounding latitude/longitude precision

Tjantik

New Member
I am trying to round latitude and longitude from 4 significant digits of precision to 1. The database contains lat/long formatted in DMS, like this:


41 43 49.5130 -122 32 44.0820


To bring it into the software I am working in, it needs to be formatted with only one degree of precision like this:


41 43 49.6 -122 32 44.1


I am super new at Excel -but have perused the topics all weekend- so I am at a loss as to how to create a function to do this. In my scribblings, I came up with if(d2,13)>=5then(d2,12)+1 (working on the longitude) but I know that's not the formula, it just, in a rudimentary way explains what I would like to do.
 

Hui

Excel Ninja
Staff member
Tjantik

I have assumed you have a table of Lats and Logs in Columns A and B

So in C2 try

=MID(A2,1,FIND(".",A2)-3)&TEXT(ROUND(RIGHT(A2,LEN(A2)-FIND(".",A2)+3),1),".0")

Copy across to D2

Then Copy C2:D2 down to the end of your data


Warning: The above is only good for rounding the Seconds part of your Lats/Longs

If you present a number like 41 59 59.98 it will incorrectly get rounded up to

41 59 60.0 where as it should be 42 00 00.0
 

Hui

Excel Ninja
Staff member
Tjantik


A better solution would be a User Defined Function to do what you want

Copy and paste the code below into a code module in VBA


To use, use it as normal function

=ddmmss(A1)

or

=ddmmss("-122 05 59.96")

[pre]
Code:
Function ddmmss(target As String) As String

Dim deg As String
Dim min As String
Dim sec As String
Dim dec As String
Dim sp1 As Integer
Dim sp2 As Integer
Dim dp As Integer
Dim ts As Variant

target = Trim(target)

sp1 = InStr(1, target, " ")
sp2 = InStr(sp1 + 1, target, " ", vbTextCompare)
dp = InStr(1, target, ".")

deg = Trim(Left(target, sp1 - 1))
min = Trim(Mid(target, sp1 + 1, sp2 - sp1))
sec = Trim(Mid(target, sp2 + 1, dp - sp2 - 1))
dec = Trim(Right(target, Len(target) - dp))

ts = Round(Val(sec + "." + dec), 1)

If ts >= 60 Then
ts = ts - 60
min = min + 1
End If

If min >= 60 Then
min = min - 60
If deg < 0 Then
deg = deg - 1
Else
deg = deg + 1
End If
End If

If Int(Val(ts)) = Val(ts) Then
ts = Str(ts) & ".0"
End If

ts = Trim(ts)

If InStr(1, ts, ".") = 2 Then ts = "0" & ts
If Len(min) = 1 Then min = "0" & min

ddmmss = deg & " " & min & " " & Trim(ts)

End Function
[/pre]
 
Top