Calculate new coordinate with distance

D

D1XII

New Member
Joined
Oct 13, 2016
Messages
26
In my mind this is simple - it is probably an excel nightmare:

My company has a geographical map that needs thousands of coordinates pinned so it can be accessed through a map app. The coordinates are close - so I want to pin one coordinate and calculate the next by adding a few feet in between.

For example: 23° 9'28.19"N, 81°55'34.06"W becomes 23° 9'28.19"N, 81°55'34.48"W after adding ~10ft to the West.

In my mind the xls would be simple:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Reference Latitude
[/TD]
[TD]Reference Longitude
[/TD]
[TD]Distance (in feet)
[/TD]
[TD]Direction from Reference to New Point
[/TD]
[TD]New Lat
[/TD]
[TD]New Long
[/TD]
[/TR]
[TR]
[TD]23° 9'28.19"N
[/TD]
[TD]81°55'34.06"W
[/TD]
[TD]10
[/TD]
[TD]West
[/TD]
[TD]23° 9'28.19"N
[/TD]
[TD]81°55'34.48"W
[/TD]
[/TR]
</tbody>[/TABLE]


I am wrecking my brain to figure something out - I definitely cannot pin 70K points on a map manually :eeek: Advanced appreciation!
 
Sort by date Sort by votes
It assumes that moving E/W is traveling along a parallel, and it will break if you cross the poles.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
A little cleanup to work over the poles and across E/W hemispheres:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
Lat​
[/td][td="bgcolor:#F3F3F3"]
Long​
[/td][td="bgcolor:#F3F3F3"]
Dist​
[/td][td="bgcolor:#F3F3F3"]
Dir​
[/td][td="bgcolor:#F3F3F3"]
New Lat​
[/td][td="bgcolor:#F3F3F3"]
New Long​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]
45°00'00.00"N​
[/td][td]
90°00'00.00"W​
[/td][td]
100​
[/td][td]
N​
[/td][td="bgcolor:#CCFFCC"]
45°00'00.99"N​
[/td][td="bgcolor:#CCFFFF"]
90°00'00.00"W​
[/td][td][/td][td="bgcolor:#CCFFCC"]E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft", "m") / 111120, 0, -CONVERT(C2, "ft", "m") / 111120, 0), TRUE)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]
45°00'00.00"N​
[/td][td]
90°00'00.00"W​
[/td][td]
100​
[/td][td]
E​
[/td][td="bgcolor:#CCFFCC"]
45°00'00.00"N​
[/td][td="bgcolor:#CCFFFF"]
89°59'58.60"W​
[/td][td][/td][td="bgcolor:#CCFFFF"]F2: =Deg2DMS(DMS2Deg(B2) + CHOOSE(SEARCH(D2, "NESW"), 0, CONVERT(C2, "ft", "m") / 111120 / COS(RADIANS(DMS2Deg(A2))), 0, -CONVERT(C2, "ft", "m") / 111120 / COS(RADIANS(DMS2Deg(A2)))), FALSE)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
45°00'00.00"N​
[/td][td]
90°00'00.00"W​
[/td][td]
100​
[/td][td]
S​
[/td][td="bgcolor:#CCFFCC"]
44°59'59.01"N​
[/td][td="bgcolor:#CCFFFF"]
90°00'00.00"W​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
45°00'00.00"N​
[/td][td]
90°00'00.00"W​
[/td][td]
100​
[/td][td]
W​
[/td][td="bgcolor:#CCFFCC"]
45°00'00.00"N​
[/td][td="bgcolor:#CCFFFF"]
90°00'01.40"W​
[/td][td][/td][td][/td][/tr]
[/table]

Code: 
Function DMS2Deg(ByVal s As String) As Double
  ' Converts d° mm' ss.ss{N|E|S|W}" to decimal degrees

  Dim iSgn          As Long

  s = Replace$(s, " ", "")
  Select Case UCase(Right$(s, 1))
    Case "N", "E"
      iSgn = 1
      s = Left$(s, Len(s) - 1)
    Case "S", "W"
      iSgn = -1
      s = Left$(s, Len(s) - 1)
  End Select

  s = Replace$(s, "°", "+")        ' replace the degree sign
  s = Replace$(s, "'", "/60+")     ' replace the minute sign (single quote)
  s = Replace$(s, """", "/3600")   ' replace the second sign (if double quote)
  s = Replace$(s, "''", "/3600")   ' replace the second sign (if two single quotes)
  If Left$(s, 1) = "-" Then
    DMS2Deg = -iSgn * Evaluate(Mid$(s, 2))
  Else
    DMS2Deg = iSgn * Evaluate(s)
  End If
End Function

Function Deg2DMS(ByVal d As Double, bLatLon As Boolean) As String
  ' formats decimal degrees as d°mm'ss.00{N|E|S|W}
  ' bLatLong = True for latitude, False for longitude

  Dim sHem          As String ' hemisphere {N|E|S|W}
  Dim dDeg          As Double
  Dim dMin          As Double
  Dim dSec          As Double

  ' convert to 0 to 360
  d = d - Int(d / 360#) * 360#

  If bLatLon Then
    ' convert lat to +/- 90
    Select Case d
      Case Is <= 90#
        sHem = "N"
      Case Is <= 180#
        d = 180# - d
        sHem = "N"
      Case Is <= 270
        d = d - 180#
        sHem = "S"
      Case Else
        d = 360# - d
        sHem = "S"
    End Select
  Else
    ' convert lon to +/- 180
    Select Case d
      Case Is < 180#
        sHem = "E"
      Case Else
        d = 360# - d
        sHem = "W"
    End Select
  End If

  dDeg = Int(d)
  d = 60# * (d - Int(d))
  dMin = Int(d)
  dSec = 60# * (d - Int(d))
  Deg2DMS = Format(dDeg, "0°") & Format(dMin, "00'") & Format(dSec, "00.00\""") & sHem
End Function
 
Upvote 0
shg said:
I think this is correct:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]
A​
[/TD]
[TD="bgcolor: #C0C0C0"]
B​
[/TD]
[TD="bgcolor: #C0C0C0"]
C​
[/TD]
[TD="bgcolor: #C0C0C0"]
D​
[/TD]
[TD="bgcolor: #C0C0C0"]
E​
[/TD]
[TD="bgcolor: #C0C0C0"]
F​
[/TD]
[TD="bgcolor: #C0C0C0"]
G​
[/TD]
[TD="bgcolor: #C0C0C0"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
1​
[/TD]
[TD="bgcolor: #F3F3F3"]
Lat​
[/TD]
[TD="bgcolor: #F3F3F3"]
Long​
[/TD]
[TD="bgcolor: #F3F3F3"]
Dist​
[/TD]
[TD="bgcolor: #F3F3F3"]
Dir​
[/TD]
[TD="bgcolor: #F3F3F3"]
New Lat​
[/TD]
[TD="bgcolor: #F3F3F3"]
New Long​
[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
2​
[/TD]
[TD]
23° 9'28.19"N​
[/TD]
[TD]
81°55'34.06"W​
[/TD]
[TD]
10​
[/TD]
[TD]
N​
[/TD]
[TD="bgcolor: #CCFFCC"]
23°9'28.29"N​
[/TD]
[TD="bgcolor: #CCFFFF"]
81°55'34.06"S​
[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft","m") / 111120, 0, -CONVERT(C2, "ft","m") / 111120, 0), "NS")[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
3​
[/TD]
[TD]
23° 9'28.19"N​
[/TD]
[TD]
81°55'34.06"W​
[/TD]
[TD]
10​
[/TD]
[TD]
E​
[/TD]
[TD="bgcolor: #CCFFCC"]
23°9'28.19"N​
[/TD]
[TD="bgcolor: #CCFFFF"]
81°55'33.95"S​
[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFFF"]F2: =Deg2DMS(DMS2Deg(B2) + CHOOSE(SEARCH(D2, "NESW"), 0, CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2))), 0, -CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2)))), "NS")[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
4​
[/TD]
[TD]
23° 9'28.19"N​
[/TD]
[TD]
81°55'34.06"W​
[/TD]
[TD]
10​
[/TD]
[TD]
S​
[/TD]
[TD="bgcolor: #CCFFCC"]
23°9'28.09"N​
[/TD]
[TD="bgcolor: #CCFFFF"]
81°55'34.06"S​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
5​
[/TD]
[TD]
23° 9'28.19"N​
[/TD]
[TD]
81°55'34.06"W​
[/TD]
[TD]
10​
[/TD]
[TD]
W​
[/TD]
[TD="bgcolor: #CCFFCC"]
23°9'28.19"N​
[/TD]
[TD="bgcolor: #CCFFFF"]
81°55'34.17"S​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHG!! You are amazing! I am going to test this right now. I have been pinning points with a map overlay since I couldn't figure it out. You're a genius! PM me your paypal email - I will gift you for your assistance or send you an amazon card or something at least! Thanks man! I will let you know how this works out. Blessings.
Click to expand...
 
Upvote 0
Let me know how it works out. It could be modified to work on the ellipsoid for movement in cardinal directions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
Z
  • Locked
  • Question Question
Convert Latitude Longitude Coordinates > Radians > Distance in miles
Replies
1
Views
9K
Fluff
Fluff
B
  • Question Question
Calculating Distance between latitude/longitude coordinates
Replies
9
Views
21K
mogadon
M
D
  • Question Question
Convert Geo Coordinates to Decimal
Replies
0
Views
626
DJFANDANGO
D
Z
  • Question Question
Convert Longitude & Latitude Start and End Coordinates into Nautical Miles (no API)
Replies
6
Views
2K
zgoldflo
Z

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top