Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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.

  4. #4
    Platinum Lounger
    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) =

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: latitude and longitude (all)

    An alternative formula for use with decimal degrees is:
    =2*ASIN(SQRT((SIN(RADIANS(Lat1-Lat2)/2))^2+COS(RADIANS(Lat1))*COS(RADIANS(Lat2))*(SIN(R ADIANS(Long1-Long2)/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.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Platinum Lounger
    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

  7. #7
    3 Star Lounger
    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...

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •