Calculate distance in km between 2 coordinates (longitude ,latitude)

[Samir_]

Hi
I need a formula in excel 365 to find the distance in km between two coordinates (longitude ,latitude)

I had tried these twos formulas
=6371 * ACOS(COS(RADIANS(90-B2)) * COS(RADIANS(90-B3)) + SIN(RADIANS(90-B2)) * SIN(RADIANS(90-B3)) * COS(RADIANS(A2-A3)))
=2*ASIN(SQRT((SIN((B3-B2)/2))^2+COS(B2)*COS(B3)*(SIN((A3-A2)/2))^2))*6371

But they are not giving the same result and they did not match the result in google maps

TIA

-Samir

 

[Georgiboy]

Are you looking at the distance in a straight line or via roads?

Do you have a Google API key/ account?
Do you have a HERE maps API key/ account?

 

[Samir_]

Are you looking at the distance in a straight line or via roads?

I need straight line distance

Do you have a Google API key/ account?
Do you have a HERE maps API key/ account?

No I don't have any of the above

 

[Georgiboy]

The below seems to do it, which is what you had:

Book1
	A	B	C	D	E	F
1		Lat	Long		KM's	Miles
2	From	51.40671	0.4093265		8.018291	4.982334
3	To	51.36638	0.5051189
Sheet1

Cell Formulas
Range	Formula
E2	E2	=6371*ACOS(COS(RADIANS(90-B2))*COS(RADIANS(90-B3))+SIN(RADIANS(90-B2))*SIN(RADIANS(90-B3))*COS(RADIANS(C2-C3)))
F2	F2	=E2*0.621371

Checked on the below
Calculate distance and bearing between two Latitude/Longitude points using haversine formula in JavaScript

Maybe you could explain more and possibly provide the lat longs you are working with and the discrepancy you are having?

 

[Samir_]

The below seems to do it, which is what you had:

Book1
	A	B	C	D	E	F
1		Lat	Long		KM's	Miles
2	From	51.40671	0.4093265		8.018291	4.982334
3	To	51.36638	0.5051189
Sheet1

Cell Formulas
Range	Formula
E2	E2	=6371*ACOS(COS(RADIANS(90-B2))*COS(RADIANS(90-B3))+SIN(RADIANS(90-B2))*SIN(RADIANS(90-B3))*COS(RADIANS(C2-C3)))
F2	F2	=E2*0.621371

Checked on the below
Calculate distance and bearing between two Latitude/Longitude points using haversine formula in JavaScript

Maybe you could explain more and possibly provide the lat longs you are working with and the discrepancy you are having?

Thank you. that's works well and the link was very helpful

 

[Georgiboy]

You are welcome, thanks for the feedback.