Finding the distance between two GPS locations

Morning,

I'm hoping you guys can point out the error I have here? As part of my research I'm including a variable with the distance (in miles) from the capital of my donor country to the capital of every other country in the world. I've been trying to use the Haversine formula, found here: http://andrew.hedges.name/experiments/haversine/ but I can't get it to work in excel.

Formula:
dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2( sqrt(a), sqrt(1-a) )
d = R * c (where R is the radius of the Earth 6373 km)

My Excel:
a =(SIN(dlat/2)^2+(COS(Capitals[@Latitude])*(COS(lat2)*((SIN(dlon/2))^2))))
c =2*(ATAN2(SQRT(a), SQRT(1-a)))
d =R*c

As a test I've been using

New Zealand
Lat:-41.29
Long: 174.78

Afghanistan
Lat: 34.53
Long: 69.17

for output I get
a = 0.332632
c = 1.912...
d = 12182.13

whereas the answer is actually 13650.679. I suspect the issue is to do with degrees to radians but I'm not sure how to incorporate that into the formula?

Why are you using the radius of the earth in km if you want the distance in miles (3960) ?

Here's the formula I have in one of my files:

3960*ACOS(COS(RADIANS(90-C4))*COS(RADIANS(90-E4))+SIN(RADIANS(90-C4))*SIN(RADIANS(90-E4))*COS(RADIANS(D4-F4)))

where C4 = lat1, D4=long1, E4 = lat2 and F4=long2.

Hope this helps.

Pete

Using your values for New Zealand and Afghanistan, I get 13650.68 km or 8482.14 miles.

Pete

Hey this works for me, thanks a bunch.

Primarily the data will be used in miles, but there is some other work for which I need it in km (which I am more comfortable using) so I just got a bit turned around.

Is this formula will calculate distance for small distance < 100 mtrs.

I believe that it's accurate to around 0.3% as it's a spherical calculation, whereas the Earth is slightly elliptical. It will tend to overestimate trans-polar distances and underestimate trans-equatorial distances.

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you, and leave a comment if you wish to, by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete