Calculating distance between two latitude longitude points

[mjseim]

I didn't think this would be so hard... but gezz! I'm not sure if this is more of a math question than an excel question, but since my weapon of choice is Excel I thought I'd give this a try.

I have been searching and searching for a formula that will derive the distance between two latitude longitude points.

My data is in the following format:
Lat Long
Origin: 44.844263 -92.914803
Destination: 44.822075 -92.912498

I have tried using the following formulas, which I found online, but it is clear I am using them incorrectly.

:::::Haversine formula:::::
R = earth’s radius (mean radius = 6,371km)
difflat = lat2 - lat1
difflong = long2 - long1
a = sin²(difflat/2) + cos(lat1) x cos(lat2) x sin²(diflong/2)
c = 2 x atan2(sqrt(a), sqrt(1-a))
d = R x c

:::::Spherical law of cosines:::::
d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R


If anyone can help me out with this I sure would appreciate it. I've already wasted hours trying to figure this out.
Thanks!

 

[Andrew Poulsom]

I think:

Book9
	A	B	C	D	E
1		Lat	Long	R	6,371
2	Origin:	44.844263	-92.914803	a	3.76948E-08
3	Destination:	44.822075	-92.912498	c	0.000388303
4				d	2.473879278
Sheet1

Formulas:

E2: =SIN(ABS(B3-B2)*PI()/180/2)^2+COS(B2*PI()/180)*COS(B3*PI()/180)*SIN(ABS(C3-C2)*PI()/180/2)^2

E3: =2*ATAN2(SQRT(1-E2),SQRT(E2))

E4: =E1*E3

 

[mjseim]

OH I COULD KISS YOU!!!!

It appears to be working well. Perhaps I should get to know conversions a little better... or at least know what the heck I'm dealing with before I start diving into things like this.

Thank you very much.

 

[DaveR]

Or alternatively you could just use Pythagarus Method (Which will probably be close enough) i.e.

distance = square-root ( (Xb-Xa)2 + (Yb - Ya)2 )

Or as I have it in an Excel Formula

=IF(AL34="",0,(SQRT((VLOOKUP(LEFT(AL34,4),'Postcode data'!$A$2:$E$3221,2)-VLOOKUP(LEFT(AJ34,4),'Postcode data'!$A$2:$E$3221,2))^2+((VLOOKUP(LEFT(AL34,4),'Postcode data'!$A$2:$E$3221,3)-VLOOKUP(LEFT(AJ34,4),'Postcode data'!$A$2:$E$3221,3))^2))/1000)/1.6093)

Which looks up the X,Y details from a UK postcode (on another worksheet) and then works out the difference between the two.

Happy to send it to you if you want it.

 

[mjseim]

I'd love it if you'd send it over. My email is mjseim@centexhomes.com

Thank you.

 

[nigs]

Could i also have a copy of that excel formula as i am trying to do the same thing.
many thanks: nigele@britishlead.co.uk

 

[mjseim]

If anyone still stumbles on this thread as I just did, this formula seems easier these days for me.

Miles:
=ACOS(COS(RADIANS(90-Lat1)) * COS(RADIANS(90-Lat2)) + SIN(RADIANS(90-Lat1)) * SIN(RADIANS(90-Lat2)) * COS(RADIANS(Long1-Long2))) * 3959

Kilometers:
=ACOS(COS(RADIANS(90-Lat1)) * COS(RADIANS(90-Lat2)) + SIN(RADIANS(90-Lat1)) * SIN(RADIANS(90-Lat2)) * COS(RADIANS(Long1-Long2))) * 6371

Source:
macros - Driving distance between two coordinates - Stack Overflow