Calculate new coordinate with distance

[D1XII]

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:

Reference Latitude	Reference Longitude	Distance (in feet)	Direction from Reference to New Point	New Lat	New Long
23° 9'28.19"N	81°55'34.06"W	10	West	23° 9'28.19"N	81°55'34.48"W

<tbody>
</tbody>

I am wrecking my brain to figure something out - I definitely cannot pin 70K points on a map manually Advanced appreciation!

 

[Gerald Higgins]

Let's assume that 10 ft does actually = 0.42 seconds - I don't know if that's right or not.

What exactly do you need help with ?

Do you need help with writing a formula that takes a set of co-ordinates, and adds 10 ft (or some other distance) to them and comes up with some new co-ordinates ?

 

[Kerryx]

looks like you'd have to reverse this scenario?
https://sciencing.com/convert-latitude-longtitude-feet-2724.html

 

[D1XII]

Exactly. Assuming 10ft = .42 seconds I would need the formula to add .42 second to the reference coordinate and to each subsequent coordinate calculation.

 

[D1XII]

Let's assume that 10 ft does actually = 0.42 seconds - I don't know if that's right or not.

What exactly do you need help with ?

Do you need help with writing a formula that takes a set of co-ordinates, and adds 10 ft (or some other distance) to them and comes up with some new co-ordinates ?

Yes, Gerald. That is exactly it. Do you have any ideas? The more I attempt, the more confused I am getting.

 

[shg]

Are the points always directly N/S/E/W of the starting point, and can you assume the earth is a sphere? If so, that's straightforward.

 

[Gerald Higgins]

Also, can you tell us please how exactly your co-ordinates are stored in your Excel worksheet ?

 

[D1XII]

Are the points always directly N/S/E/W of the starting point, and can you assume the earth is a sphere? If so, that's straightforward.

Hi shg - yes the points will always be N,S,E or W and the earth is a normal sphere.

 

[D1XII]

Also, can you tell us please how exactly your co-ordinates are stored in your Excel worksheet ?

Yes, the coordinates are in LAT/LONG [DMS] format and the output of the new coordinates would need to follow so I can import them back into the software. I don't know if that answers the question or not?

 

[shg]

I think this is correct:

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Lat​	Long​	Dist​	Dir​	New Lat​	New Long​	​	​
2​	23° 9'28.19"N​	81°55'34.06"W​	10​	N​	23°9'28.29"N​	81°55'34.06"S​		E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft","m") / 111120, 0, -CONVERT(C2, "ft","m") / 111120, 0), "NS")
3​	23° 9'28.19"N​	81°55'34.06"W​	10​	E​	23°9'28.19"N​	81°55'33.95"S​		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")
4​	23° 9'28.19"N​	81°55'34.06"W​	10​	S​	23°9'28.09"N​	81°55'34.06"S​
5​	23° 9'28.19"N​	81°55'34.06"W​	10​	W​	23°9'28.19"N​	81°55'34.17"S​

A uses a pair of UDFs:

Function DMS2Deg(ByVal s As String) As Double
    ' Converts dd° 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
    s = Replace$(s, """", "/3600")   ' replace the second sign (double quote)
    s = Replace$(s, "''", "/3600")   ' replace the second sign (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, sLL As String) As String
  Dim sDir As String
  Dim dDeg As Double
  Dim dMin As Double
  Dim dSec As Double
  
  ' sLL is "NS" for latitude and "EW" for latitude
  
  If d >= 0 Then
    sDir = Left(sLL, 1)
  Else
    sDir = Mid(sLL, 2, 1)
    d = -d
  End If

  dDeg = Int(d)
  d = 60# * (d - Int(d))
  dMin = Int(d)
  dSec = 60# * (d - Int(d))
  Deg2DMS = dDeg & "°" & dMin & "'" & Format(dSec, "0.00") & """" & sDir
End Function