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?

1

4 Answers

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.

1

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: 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

Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at

0

If you reverse the arguments below:

Was: G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371

Is: G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371

You should get the correct answer. I hope this helps!

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy