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-bMar 4, 2014 at 16:54
-
By which unet, will return the distance?– JeedApr 2, 2017 at 8:21
-
@Jeed Any unit would be fine, probably miles in this case.– b-rad-bJun 7, 2017 at 13:24
Add a comment
|
2 Answers
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.
answered Mar 4, 2014 at 16:55
-
3
-
@blah238 Thanks for your suggestion. I've added a short how-to in order to make it working. Mar 5, 2014 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?– KerryNov 24, 2015 at 1:11
-
@Kerry It's kilometers. Also, where the formula says "Sqr" it should be "Sqrt".– ddunn801Jan 24, 2018 at 19:25
-
The unit of measure of distances is kilometers, just like the Earth radius. Jan 26, 2018 at 11:49
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"
