Performing Distance Calculation using Excel?

Question

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 04 '14 at 16:54 — b-rad-b, Mar 04 '14 at 16:54
By which unet, will return the distance? – Jeed Apr 02 '17 at 08:21 — Jeed, Apr 02 '17 at 08:21
@Jeed Any unit would be fine, probably miles in this case. – b-rad-b Jun 07 '17 at 13:24 — b-rad-b, Jun 07 '17 at 13:24

score 17 · Answer 1 · edited Sep 27 '19 at 06:18

17

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

edited Sep 27 '19 at 06:18

answered Mar 04 '14 at 16:55

Antonio Falciano

14,333
2
36
66

3

Direct link: http://www.codecodex.com/wiki/Calculate_Distance_Between_Two_Points_on_a_Globe#Excel – blah238 Mar 04 '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 05 '14 at 09: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 01: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

score 3 · Answer 2 · answered Mar 05 '14 at 08:12

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"

Performing Distance Calculation using Excel?

2 Answers2

Linked