distance between 2 coordinates

[6diegodiego9]

Consider this two point on the map:

[TABLE="width: 210"]
<tbody>[TR]
[TD="class: xl65, width: 116"]latitude[/TD]
[TD="class: xl65, width: 94"]longitude[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 116"]45.61782[/TD]
[TD="class: xl65, width: 94"]9.41737[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 116"]45.48568[/TD]
[TD="class: xl65, width: 94"]9.20181[/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="width: 446"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]latitude[/TD]
[TD]longitude[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6371[/TD]
[/TR]
[TR]
[TD]45.61782[/TD]
[TD]9.41737[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0,842697[/TD]
[/TR]
[TR]
[TD]45.6176612[/TD]
[TD]9.4175847[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2,325941[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14818,57[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3.63778E-12[/TD]
[/TR]
[TR]
[TD="align: right"]3.81459E-06[/TD]
[/TR]
[TR]
[TD="align: right"]0.024302778[/TD]
[/TR]
</tbody>[/TABLE]

 

[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