Distance between two sets of coordinates.

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

Check out Chip Pearson's site:

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

--
Regards,
Tom Ogilvy

"Marcus Fox" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>
>

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