Calculating GPS difference formula

moone009

Board Regular
Joined
Jan 26, 2010
Messages
82
I was wondering if anyone has a formula to calculate feet between two gps coordinates?

Latitude Longitude
42.7642733333333 -87.8000916666667
42.764265 -87.7996183333333

From the formulas that I have put together and the ones I have found online my results have varied. I have found a difference of anywhere from 20 feet all the way to 123 feet. I know for a fact these distances cannot be more than 15 feet apart. Any suggestions
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
This function computes the central angle between two points on a spherical earth:
Code:
Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008-1111
    ' Returns central angle between two point in RADIANS
    ' using Vincenty formula
 
    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#
    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double
 
    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lat2 = D2R * lat2
    dLon = D2R * (lon2 - lon1) ' delta lon
 
    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function
Multiply by the mean earth radius in the units of your choice, e.g., for feet,

=20902000*CentralAngle(A2, B2, A3, B3)

Code:
      ----A---- ----B----- --C--
  1      Lat       Lon     Dist 
  2   42.764273 -87.800092 126.8
  3   42.764265 -87.799618

I know for a fact these distances cannot be more than 15 feet apart.

Sanity check: Your points are at essentially the same latitude and differ by 0.000473 degrees in longitude. A degree of longitude is 60 nautical miles at the equator, and decreases with the cosine of latitude.

= CONVERT(0.000473 * 60 * COS(RADIANS(42.76)), "Nmi", "ft") returns ~ 126.6 feet
 
Last edited:

moone009

Board Regular
Joined
Jan 26, 2010
Messages
82
Thank you very much! I guess it could be the actual GPS unit that is recording the results.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

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
Top