very hard excel equation,, longitude latitude distance calc

you have 2 columns and 2 rows.
column A equals the latitude in decimal degrees
column B equals the longitude in decimal degrees

all of the rows equal the coordinates of a certain bldg  

example
                     column A  (lat)       Column B (long)   Column C(distance between skating rink and
                                                                                 mall)
skating rink   36.009900              -79.727086        (desired result)
mall               35.090909             - 78.789899

my coordinates are in decimal degrees
Column C will have the distance between skating rink and mall

to answer this requires some serious math i think.
like sin and cosine type stuff


jamesmetcalf74Asked:
Who is Participating?
 
BarryTiceCommented:
=ACOS(SIN(RADIANS(A1))*SIN(RADIANS(A2))+COS(RADIANS(A1))*COS(RADIANS(A2))*COS(RADIANS(B2-B1)))*6371

This is giving the answer in kilometers. If you want it in miles, instead, use this:

=ACOS(SIN(RADIANS(A1))*SIN(RADIANS(A2))+COS(RADIANS(A1))*COS(RADIANS(A2))*COS(RADIANS(B2-B1)))*3959
0
 
BarryTiceCommented:
This isn't so hard if you can make some (pretty significant) assumptions about your terrain.

Can we assume that we have a perfectly spherical world, with no hills between point A and point B?

If so, the math is relatively easy. If not, your best bet would probably be to make an Internet connection to the Google Earth API and get Google to calculate a distance for you, though that's not likely to be speedy.

If you can assume a sphere, here's your formula:
=ACOS(SIN(A1)*SIN(A2)+COS(A1)*COS(A2)*COS(B2-B1))*6371
0
 
Gašper KamenšekExcel MVPCommented:
Hi,

easy way of doing this is to calculate the difference between the coordinates in both dimentions. Then you convert those to the metrics you need (miles, meters,...) Than you actually calculate the distance with Pytagora. (lat. dif.)^2 + (long. dif.)^2 = distance^2...

The correct answer would involve Spherical cosinus and sinus and so on... Very hard math and in this case the difference in result would be a few meters...

Good luck
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
BarryTiceCommented:
(Answer is in meters, by the way.)
0
 
jamesmetcalf74Author Commented:
36.087001      -79.727086
36.084945      -79.770744


those are my test coordinates.
with the equation you gave me my answer came out to be 17.6

i confirmed the coordinates are 2.44 miles away.....  any ideas?
0
 
BarryTiceCommented:
I suspect rounding errors, James. At that distance, a lot of .999 rounding will happen in the trig, so we may have to come up with a different approach than the one listed (which comes from formulae in the Wikipedia article on great circles, confirmed elsewhere, by the way).

Strangely, when I try a variation on the formula (haversines), the haversine formula now gives 17.6, and the original formula switches to 6370.976, which is about 3.95 miles – still very wrong.

The haversine version of the formula is:
=(ASIN(SQRT(((SIN((A2-A1)/2))^2) + (COS(A1) * COS(A2) * ((SIN((B2-B1)/2))^2))))) * 2 * 6371

See the attached screenshot.

I'm still fiddling with this...
GreatCircleDistance.png
0
 
BarryTiceCommented:
I think I'm onto something here. I believe it's an issue of degrees vs. radians. Lemme beat on it a little more.
0
 
jamesmetcalf74Author Commented:
worked close enough for me!
this site rocks
never would have thunk someone on here would figure that out for me
0
 
BarryTiceCommented:
Yes, this site absolutely rocks. It's been my No. 1 go-to site for well over a decade now.

Glad I was able to help. Thanks for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.