# Excel Distance Calculation

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

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.

-
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. – afalciano Mar 5 '14 at 9:58
@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

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"

-