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?
Best Answer
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:
The result is in kilometers, for small distances I used
*1000
to display the distance in meters.To display distances in miles, substitute
*6371
with*3958
.