Distance Between Two Points . .

J

jproffer

Well-known Member

Joined

Dec 15, 2004

Messages

2,647

• Sep 4, 2008

• #2

I don't have code, but here is a formula.

=ACOS((SIN(Lat1)*SIN(Lat2))+(COS(Lat1)*COS(Lat2)*COS(long2-long1)))

Then that result multiplied by 3963.191 (diameter of the earth at the equator).

Google "Great Circle Distance" and you'll find similar formulas.

If that's the only points you needed to calc. the distance is 167.6521489521 miles, or 269.80998000317 KM, or 885203.3464671 Feet , or...yea ok, I'm done bein stupid, but anyhow 168ish miles

 

Last edited: Sep 4, 2008

Upvote 0

S

stanleydgromjr

Banned

Joined

Jul 30, 2006

Messages

3,656

• Sep 4, 2008

• #3

BlackTiger

Give this function, by "TheClansman", "GetDistance(p1, p2)" a try:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3762338&SiteID=1


Have a great day,
Stan

 

Upvote 0

J

jproffer

Well-known Member

Joined

Dec 15, 2004

Messages

2,647

• Sep 4, 2008

• #4

OK, I have a WB with a userform (had to find it) to do this also, but I'm not allowed to post attachments (not sure why)...but feel free to PM me an email addy and I'll be happy to share.

 

Upvote 0

E

Evagrius Ponticus

Well-known Member

Joined

May 24, 2007

Messages

1,467

• Sep 4, 2008

• #5

Jproffer - dude - I'd be grateful for any help on this. I have been struggling all day to get this right. I have an excel sheet will the long and lat of all cities in US. I tried the formula you posted and prior to the final multiplication - I get 2.42 - that isn't right. Using the sample data in my initial post, below is the formula I used.

Code:

=ACOS((SIN(35.0846)*SIN(32.6621))+(COS(35.0846)*COS(32.6621)*COS((-106.716)-(-106.623))))

 

Upvote 0

J

jproffer

Well-known Member

Joined

Dec 15, 2004

Messages

2,647

• Sep 4, 2008

• #6

No that isn't right...I'm sorry I left out a FAIRLY important piece of information.

The Lat and Long has to be in Radians which I believe is Deg/180*Pi, but at any rate its

(Worksheet Function) Radians(whatever cell your lat or long are in) each one seperately, needless to say.

 

Upvote 0

E

Evagrius Ponticus

Well-known Member

Joined

May 24, 2007

Messages

1,467

• Sep 4, 2008

• #7

great - now I get the same answer as you did 168m. Yahoo maps has 224 - I am guessing that driving distance is different than then how we figured it out?

 

Upvote 0

J

jproffer

Well-known Member

Joined

Dec 15, 2004

Messages

2,647

• Sep 4, 2008

• #8

I'm sure it is...seein as how you have to follow those pesky roads and all ...just messin.

Great circle distance is as the crow flies but even still...I can't imagine its 56 miles more over 168 miles...but maybe. Winding (very winding) roads???

 

Upvote 0

E

Evagrius Ponticus

Well-known Member

Joined

May 24, 2007

Messages

1,467

• Sep 4, 2008

• #9

Hmm, as the crow flies huh, well, I hope the crow isn't carrying anything to slow him down )

 

Upvote 0