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

Question

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:

That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.

The data looks like that:

  |        A        B    C    D    E   F    G
--|-------------------------------------------
0 |      LAT      LON rLAT dLAT dLON   a DIST
1 | 52.39964 13.04729  ...  ...  ... ...     
2 | 52.39985 13.04802  ...  ...  ... ...  ???
3 | 52.40116 13.04744  ...  ...  ... ...  ???
4 | 52.40147 13.04722  ...  ...  ... ...  ???
5 | 52.40163 13.04685  ...  ...  ... ...  ???
6 |      ...      ...  ...  ...  ... ...  ...

Now, what I have tried is the following for the field G2 in LibreOffice:

C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371

The result for G2 is20015 which is quite... wrong.

How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?

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

Comments

[Brian Adkins]

Quick google search : bluemm.blogspot.com/2007/01/…

[fixer1234]

Can you add a brief explanation of why this fixes the problem? Thanks.

[WGroleau]

For nautical miles, the multiplier is approximately 3439.4