# Finding the nearest location from list of latitude Longitude

1. ## 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. ## 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.

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,

4. ## 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!

5. ## Re: Finding the nearest location from list of latitude Longitude

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

6. ## 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. ## 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. ## Re: Finding the nearest location from list of latitude Longitude

Originally Posted by Eric.xls
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)

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.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

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

#### 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