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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
BarryTiceCommented:
(Answer is in meters, by the way.)
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.