Distance between two sets of coordinates.

[Marcus Fox]

I am looking into writing a spreadsheet that will calculate the shortest
distance over the Earth's surface between two (or a table containing a
number of) cities. These cities will be referenced by their latitude and
longtitude coordinates in the format dd mm ss.sss.

The closest formula I have been able to find is the Haversine formula. d =
R.c

R = mean radius of the earth (6,371 km)

d(lat) = lat2 - lat1
d(long) = long2 - long1
a = sin^2(d(lat)/2) + cos(lat1).cos(lat2)sin^2(d(long)/2)
c = 2 arcsin(sqrt(a)

Assuming lat1 is in A1, long1 is in B1 and lat2 is in A2, long2 is in B2
would something like

=6371*(2arcsin(sqrt(sin^2(A2-A1)+cos(A1)*cos(A2)sin^2(B2-B1)/2))

be acceptable? How do I proceed further?

Marcus

 

[Tom Ogilvy]

Check out Chip Pearson's site:

http://www.cpearson.com/excel/latlong.htm

 

[prabhuraaman]

I cannot understand your formula as the parenthesis doesn't match

This should work
6371*(2*ASIN(SQRT(SIN(A2-A1)^2))+COS(A1)*COS(A2)*SIN(B2-B1)/2^2)
Else,you could use the following excel functions
sin = SIN
cos = COS
arcsin = ASIN
sqrt=SQRT