8

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.

  • I'm looking for minimum distance. – b-rad-b Mar 4 '14 at 16:54
  • By which unet, will return the distance? – Jeed Apr 2 '17 at 8:21
  • @Jeed Any unit would be fine, probably miles in this case. – b-rad-b Jun 7 '17 at 13:24
16

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.

Public Function getDistance(latitude1, longitude1, latitude2, longitude2)  
earth_radius = 6371  
Pi = 3.14159265  
deg2rad = Pi / 180  

dLat = deg2rad * (latitude2 - latitude1)  
dLon = deg2rad * (longitude2 - longitude1)  

a = Sin(dLat / 2) * Sin(dLat / 2) + Cos(deg2rad * latitude1) * Cos(deg2rad * latitude2) * Sin(dLon / 2) * Sin(dLon / 2)  
c = 2 * WorksheetFunction.Asin(Sqr(a))  

d = earth_radius * c  

getDistance = d  

End Function

There will be a new custom getDistance function (unit = kilometer) 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

  • 3
    Direct link: codecodex.com/wiki/… – blah238 Mar 4 '14 at 21:34
  • @blah238 Thanks for your suggestion. I've added a short how-to in order to make it working. – Antonio Falciano Mar 5 '14 at 9:58
  • 1
    @afalciano Thanks for the walk through & code. My question though is what are the ultimate units from this equation? is it meters? – Kerry Nov 24 '15 at 1:11
  • @Kerry It's kilometers. Also, where the formula says "Sqr" it should be "Sqrt". – ddunn801 Jan 24 '18 at 19:25
  • The unit of measure of distances is kilometers, just like the Earth radius. – Antonio Falciano Jan 26 '18 at 11:49
3

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"

protected by Community Mar 23 '18 at 5:03

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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