Csdrhrt

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?

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.

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

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 https://github.com/tdjastrzebski/VincentyExcel

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!