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.

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!

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

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)

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