+ Reply to Thread
Results 1 to 8 of 8

Finding the nearest location from list of latitude Longitude

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Finding the nearest location from list of latitude Longitude

    Hi guys,

    Long time reader, first time poster, but I'm having a problem which i can't find a solution to on the forum.

    I have a list of 700 locations (names - Column A) and their latititude (Column B) and longitude (Column C), and I'm looking to return the nearest three locations to the right, along with their distance (i.e. Nearest (Column D), Distance to nearest (Column E), second nearest (Column F), etc.

    I found something similar for weather stations but I couldn't adapt as the excel spreadsheet seemed to corrupt when I was using it, and just repeatedly crashed.

    If anyone could help using formulas or VBA that would be great! The distances can be as the crow flies and it doesn't matter hugely which distance model is used, whichever is easiest really.

    Any help would be much appreciated. I can provide sample data if necessary, but it's a pretty straight forward 3 column spreadsheet at the moment

    Thanks,

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,171

    Re: Finding the nearest location from list of latitude Longitude

    To test possible concepts probably sample workbook is really usefull (unless somebody has one already prepared handy).

    PS. Usefull reference for formula to calculate the distance can be found here: http://www.movable-type.co.uk/scripts/latlong.html and then, knowing how to calculate the distance, one could use array formula to find 1st,2nd and 3rd closest point.
    Last edited by Kaper; 03-13-2014 at 02:17 PM.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Finding the nearest location from list of latitude Longitude

    Hi Kaspar, thanks for your reply. I've posted up a sample workbook; with the first 26 entries, there's just over 700 in total.

    Best,
    Attached Files Attached Files

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,171

    Re: Finding the nearest location from list of latitude Longitude

    Hi,

    But it is just using the formula, which is given in the link above.
    Excel: =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371

    (Note that here and in all subsequent code fragments, for simplicity I do not show conversions from degrees to radians; see code below for complete versions).
    Have you tried it?
    It works for a pair.

    Then there is just a question of doing in one step calculation of all distances.

    so basic formula for distance between first two points in your sheet will be:

    Please Login or Register  to view this content.
    As you want to check distances to all points you can use not B3 but $B$2:$B$27 range - it will be array formula (array - Ctrl+Shift+Enter comitted) because you want excel to examine all pairs B2-B2, B2-B3, B2-B4
    Well - there is B2-B2 - distance will be 0. so to find closest distance you have to use not MIN(list_of_values) but SMALL(list_of_values,2) in E2:

    Please Login or Register  to view this content.
    reminder: array formula!

    as you copy it down you will notice that there is one #NUMBER! error message.
    This is the problem with using double precision representation of floating points numbers.
    the argument for acos function in 7th row is jus a tiny bit bigger than 1. And of course this is invalid.
    so corrected formula for distance to closest would be for instance:
    Please Login or Register  to view this content.
    and then once you know it it will be just INDEX and MATCH to find the name of such point (wee look for the abobe calculated distance in list of distances, so again array formula):
    in D2:
    Please Login or Register  to view this content.
    I also did it for second closest SMALL(....,3) and INDEX($A$2:$A$27,MATCH(G2,... in the attachment.

    Third closest I left for you. Remember - array formulas!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-13-2014
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Finding the nearest location from list of latitude Longitude

    Hi Kaper, thank you so much, that worked perfectly! Really appreciate it.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,171

    Re: Finding the nearest location from list of latitude Longitude

    Would be nice and following forum rules if you mark the thread as solved. By the way, as a "series law" is valid also on forums, see next thread working with similar data: http://www.excelforum.com/excel-prog...les-apart.html

  7. #7
    Registered User
    Join Date
    03-10-2018
    Location
    London, England
    MS-Off Ver
    365
    Posts
    1

    Re: Finding the nearest location from list of latitude Longitude

    Hi, not sure how this works, is there a detail explanation to the formula? -On occasions I get very different results from google maps (walking)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Finding the nearest location from list of latitude Longitude

    Quote Originally Posted by Eric.xls View Post
    Hi, not sure how this works, is there a detail explanation to the formula? -On occasions I get very different results from google maps (walking)
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 05-02-2012, 01:00 AM
  2. MGRS to Longitude/Latitude
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2009, 11:26 AM
  3. Latitude/Longitude Porblem
    By edwardtong694 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 12:23 PM
  4. formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  5. Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1