Calculate distance between a pair of lat/lon coordinates

I recently had a need to calculate distance between a large number of latitude/longitude coordinate pairs. There are many options available if you want to import these in a GIS and run analysis. But there is a simpler and much more accesible way if you aren’t doing very high accuracy calculations.

Here I have a spreadsheet which implements the well-known Haversine formula to calculate distance between 2 coordinates. You can structure your point coordinates into 4 columns Lat1, Lon1, Lat2, Lon2 in decimal degrees and the distance will be calculated in meters.

You can give it a try. Just open this spreadsheet, make a copy it and play with it as you like.

The raw formula is below (Thanks to the reader Samuel who suggested it)

=2 * 6371000 * ASIN(SQRT((SIN((LAT2*(3.14159/180)-LAT1*(3.14159/180))/2))^2+COS(LAT2*(3.14159/180))*COS(LAT1*(3.14159/180))*SIN(((LONG2*(3.14159/180)-LONG1*(3.14159/180))/2))^2))

If you are working with coordinates in a spreadsheet, check out this post on converting Lat/Lon coordinates to UTM coordinates dynamically in a spreadsheet.


Leave a Comment

    • Distance is in meters. 6371000 is the radius of earth in meters. If you use the radius in any other units then the result would be in that unit.

Leave a Reply