How to do a "great circle" calculation in MS Excel or LibreOffice?

Solution 1:

As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:

=ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371

The result is in kilometers, for small distances I used *1000 to display the distance in meters.

  |        A        B      C
--|--------------------------
0 |      LAT      LON   DIST
1 | 52.39964 13.04729               
2 | 52.39985 13.04802   54.8
3 | 52.40116 13.04744  150.9
4 | 52.40147 13.04722   37.6
5 | 52.40163 13.04685   30.8
6 |      ...      ...    ...

To display distances in miles, substitute *6371 with *3958.

Solution 2:

The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.

A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at: http://lost-species.livejournal.com/38453.html The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.

Regards, J.Latham, Microsoft MVP, Excel 2006-2014