Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Is there a way (preferably in Excel) to calculate the distance (based on lat, lon) between two lists of points? My end goal would be, for example, to have a list of Starbucks and a list of McDonald's, and then show the nearest neighbor, if you will.

Thanks in advance.

share|improve this question
    
I'm looking for minimum distance. –  b-rad-b Mar 4 at 16:54

2 Answers 2

Given a list of geographic coordinate pairs, you can implement the Haversine formula directly in Excel.


The simplest way to use this (or a more accurate, but I think it's not your case) formula consists into press Alt+F11 to open the VBA Editor, click Insert --> Module and then (copy and) paste e.g. the code kindly suggested by blah238. There will be a new custom getDistance function available in your spreadsheet which accepts four parameters, i.e. the two pairs of coordinates, as follow:

getDistance(latitude1, longitude1, latitude2, longitude2)

where latitude1, longitude1, latitude2, longitude2 should be replaced by their relative cell references.

enter image description here

share|improve this answer
2  
Direct link: codecodex.com/wiki/… –  blah238 Mar 4 at 21:34
    
@blah238 Thanks for your suggestion. I've added a short how-to in order to make it working. –  afalciano Mar 5 at 9:58

A more accurate way is to use Vicenty's formula. It is based on an ellipsoid instead of a sphere. However, the previous answer will do the job if you work in a city (differences can be neglected in your case). I've found an excel vb code here just in case.

Note that if you work in a city, using "bird flight" distances could be misleading. It would be better to use network distances. A compromise is to use "Manhattan distance"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.