Results 1 to 8 of 8
Thread: latitude and longitude (all)

20040911, 02:01 #1
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
latitude and longitude (all)
I have latitude and longitude for points A and B. I want to determinte the distance between A and B in excel. Does anyone have the equation?

20040911, 02:10 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: latitude and longitude (all)
Chip Pearson has some info
Steve

20040911, 03:04 #3
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: latitude and longitude (all)
Great!
I tried it and it does just what I want.
Thanks a lot.

20040911, 03:15 #4
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: latitude and longitude (all)
I suspect that the formula presented there, while in common usage, will produce inaccurate results for small distances, largely because of the use of the inverse cosine, which loses all of its significant figures rapidly for small values.
An alternative calculation, which doesn't suffer from this, incorporates the Haversine or half versine function, defined as:
hav(x) =

20040911, 03:24 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 Posts
Re: latitude and longitude (all)
An alternative formula for use with decimal degrees is:
=2*ASIN(SQRT((SIN(RADIANS(Lat1Lat2)/2))^2+COS(RADIANS(Lat1))*COS(RADIANS(Lat2))*(SIN(R ADIANS(Long1Long2)/2))^2))*RadiusEarth
As with Chip Pearson's formula, you need to use ve values for the Lat/Long on one side of the equation if you're mixing hemispheres.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20040911, 13:19 #6
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: latitude and longitude (all)
I hadn't seen that rendition before. I'll give it some study.
There are really two separate issues involved in this. The first is to manipulate the latitudes and longitudes so that their "differences" will actually be a reflection of the smallest arc swept between the two points. This is irrespective of the units used or what hemispheres the points are in, provided their coordinates are initially manipulated appropriately to reflect this "smallest" arc.
The other issue is that using the inverse cos function, while mathematically correct, is susceptible to losing significant figures for small values. A calculation to seven significant figures cannot distinguish the cosines of any distances smaller than about one minute of arc, or 1 (nautical) mile.
A third problem (I now recall) is with Excel's ATAN2 function. This may have been a requirement specific to my own formulation, but I needed a positive radian measure (describing the sweep of the arc) from the ATAN2 function  thus a value between 0 and pi. Excel, however, returns in the range (pi/2, pi/2). While this is correct (maybe even standard, since it represents a continuous function, unlike my version) it was inappropriate to the needs of my calculations.
Anyway, a few bits & pieces of possible relevance.
Alan

20040911, 20:01 #7
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: latitude and longitude (all)
I should have also asked about calculating the _bearing_ between the same 2 points...

20040912, 02:39 #8
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 Posts
Re: latitude and longitude (all)
You might find something useful at Ed Williams' Aviation page http://williams.best.vwh.net/, where the formulae are set out in his Aviation Formulary and from where you can download excetuables and an Excel spreadsheet that uses UDFs etc he's written for doing this sort of thing.
CheersCheers,
Paul Edstein
[MS MVP  Word]