Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

haversine calculation

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?

share|improve this question
3  
Quick google search : bluemm.blogspot.com/2007/01/… –  BrianAdkins Jun 2 '13 at 15:42
    
Thanks, that worked. –  vertoe Jun 2 '13 at 17:10
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.