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.
I decided to write a simple Macro or AppScript for Google Spreadsheets, which uses the well-known Haversine formula to calculate distance between 2 coordinates. Once the script is saved, the user of the spreadsheet can use the function ‘distance()’ just the way one uses any of the built-in functions.
You can give it a try. Just open this spreadsheet, make a copy it and play with it as you like.
Update: A reader found another simple way to do the same without the need of appscript.
I implemented the Haversine formula from within Google Sheets without having to create a formula, which may be easier for some calculations. Where you can re place LAT1, LAT2, LONG1, and LONG2 with the correct cell references for your sheet to latitude and longitude coordinates in degrees.Samuel
=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))