distance between 2 coordinates

[6diegodiego9]

Consider this two point on the map:

latitude	longitude
45.61782	9.41737
45.48568	9.20181

<tbody>
</tbody>

The distance between each other is about 22km.
What's the formula to calculate that?

I tried different formulas found online but every one returned absurd values...

 

[cunningAce]

Found this on another thread and seems to work nicely,

Earths Radius Cell E1 = 6371
Cells A2 and A3 - latitude co ordinates
Cells B2 and B3 - longitude co ordinates

Cell E2 - =SIN(ABS(A3-A2)*PI()/180/2)^2+COS(A2*PI()/180)*COS(A3*PI()/180)*SIN(ABS(B3-B2)*PI()/180/2)^2
Cell E3 - =2*ATAN2(SQRT(1-E2),SQRT(E2))
Cell E4 - =E1*E3

Result =22.3074169491921

 

[6diegodiego9]

Still absurd results:

latitude	longitude			6371
45.61782	9.41737			0,842697
45.6176612	9.4175847			2,325941
				14818,57

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[cunningAce]

Not sure what's happening then,
Works on mine,
I'm getting the below results.


earths radius 6371 Cell E1
3.06495E-06 Cell E2
0.0035014 Cell E3
22.30741695 Cell e4

 

[6diegodiego9]

Can you see any difference?

 

[cunningAce]

Sorry I cant see the problem here,

the co-ordinates in your last post are different from your first.
Even with these new ones I get the following. I checked this with an online calculator and the distance is correct

3.63778E-12

3.81459E-06

0.024302778

<colgroup><col></colgroup><tbody>
</tbody>

 

[6diegodiego9]

using the coordinates of my first post:

I still don't understand why does the difference happen

 

[Haluk]

cunningAce's results seem to be correct.

You can use this single formula to calculate the distance;

=6371*((2*ASIN(SQRT((SIN((RADIANS(A5)-RADIANS(A6))/2)^2)+COS(RADIANS(A5))*COS(RADIANS(A6))*(SIN((RADIANS(B5)-RADIANS(B6))/2)^2)))))

 

[Haluk]

By the way, try to change your LAT and LON data as;

45,61782 and 9,41737

I mean; use comma (",") instead of dot (".") for the decimal point indicator..... which depends on your Excel settings.

 

[wideboydixon]

Book1
	A	B	C	D	E	F
1	Latitude	Longitude		Radius	6371	km
2	45.61782	9.41737		a	3.06495E-06
3	45.48568	9.20181		c	0.0035014
4				Distance	22.30741695	km
Sheet2

Cell Formulas
Range	Formula
E2	=SIN((A3-A2)*PI()/360)^2+COS(A2*PI()/180)*COS(A3*PI()/180)*SIN((B3-B2)*PI()/360)^2
E3	=2*ATAN2(SQRT(1-E2),SQRT(E2))
E4	=E3*E1

WBD