PPRuNe Forums

Go Back   PPRuNe Forums > Misc. Forums > Computer/Internet Issues & Troubleshooting
Forgotten your Username/Password?

Computer/Internet Issues & Troubleshooting Anyone with questions about the terribly complex world of computers or the internet should try here. We will also try and help with troubleshooting any technical problems you may have with the forums.


Reply
 
Thread Tools
Old 5th Oct 2006, 08:06   #1 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
Excel Distance Calculator Formulae

Hi guys,
Would anyone happen to know where I can find a ready made formula for Excel that can calculate the distance between 2 sets of WGS84 Lat and Long coordinates?
Cheers,

Ox
Oxeagle is offline   Reply
Old 5th Oct 2006, 10:57   #2 (permalink)
mdc
 
Join Date: Dec 2004
Location: Cambridge
Posts: 79
Is this what your looking for http://www.tma.dk/gps/#calcdist
mdc is offline   Reply
Old 5th Oct 2006, 11:32   #3 (permalink)
 
Join Date: Aug 1999
Location: In the pension queue, Lancashire, UK
Age: 71
Posts: 210
Not familiar with WGS84 co-ordinates, but that looks like the calculation I use:

=SUM(60*ACOS((SIN(Lat1*PI()/180)*SIN(Lat2*PI()/180)+COS(Lat1*PI()/180)*COS(Lat2*PI()/180)*COS((Long2-Long1)*PI()/180)))*180/PI())

(which you can cut and paste directly into a cell) where one point is at Lat1, Long1 and the other is at Lat2, Long2. The result is in nautical miles.


As the comment in the reference says, you have to convert degrees, minutes and seconds to decimals of degrees, although there is probably a quick way around this that I haven't bothered with. You also have to be careful when the points are on opposite sides of 0 or 180 degrees longitude or of the equator - in each case one set of numbers has to be negative, the other positive. For example, taking west of the Greenich meridian and south of the equator as negative;
Manchester is +53.50, -2.25
Kuala Lumpur is +3.13, +101.70
Santiago de Chile is -33.50, -70.67

If I remember correctly, I got that out of the instruction book for a Hewlett Packard HP35 calculator in the 1980s!

GG

Last edited by Groundgripper; 5th Oct 2006 at 11:51. Reason: Edited for clarity (and history!)
Groundgripper is online now   Reply
Old 5th Oct 2006, 12:30   #4 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
That's perfect Groundgripper, thanks very much! Also thanks to mdc for your time as well
Oxeagle is offline   Reply
Old 6th Oct 2006, 12:46   #5 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
Now on to the next challenge - an Excel formula to work out the track between two sets of coordinates. Any ideas Groundgripper?

Cheers
Oxeagle is offline   Reply
Old 6th Oct 2006, 13:27   #6 (permalink)
 
Join Date: Mar 2004
Location: Bournemouth, UK
Age: 44
Posts: 137
[pedantic mode on] The formula provided does not provide distance on a WGS84 sphereiod, but on a standard sphere. One's round, that other one's roundish.[/pedantic mode off]
Stoney X is offline   Reply
Old 6th Oct 2006, 16:17   #7 (permalink)
 
Join Date: Jan 2001
Location: Canada
Posts: 305
Although not exactly what you were looking for, this site will produce great circle distances between lat/long points and by various aviation designators.
http://gc.kls2.com/
innuendo is offline   Reply
Old 7th Oct 2006, 16:44   #8 (permalink)
mdc
 
Join Date: Dec 2004
Location: Cambridge
Posts: 79
Oxeagle,

On the previous link I've mentioned I thought there was formula to calculate this.
mdc is offline   Reply
Old 8th Oct 2006, 09:29   #9 (permalink)
 
Join Date: Dec 2005
Location: UK Midlands
Posts: 140
Hi OA

This is what I use. Sorry about the brackets!

Initial True Track = arcos [ (sin LatB - sin LatA* cos(Dist/60))/(sin (Dist/60) cos LatA)]


if sin (LongA - LongB) < 0
then (360-Initial True Track)
is used

Where:

Start Position
Lat A
Long A

Finish Position
Lat B
Long B

Last edited by Mornington Crescent; 8th Oct 2006 at 09:36. Reason: Update
Mornington Crescent is offline   Reply
Old 8th Oct 2006, 21:25   #10 (permalink)
 
Join Date: Aug 1999
Location: In the pension queue, Lancashire, UK
Age: 71
Posts: 210
Quote:
Now on to the next challenge - an Excel formula to work out the track between two sets of coordinates. Any ideas Groundgripper?
Er, no! And sorry for the delay in responding - busy this weekend. As you will have seen, there are greater minds than mine responding to your query.
Stoney X: yes, I realised that it was for a true sphere but had (and still have) no idea about what difference that would make - my interest has never required that accurate an answer! I did google on WGS84 and read this article in Wikipedia, but halfway through everything went blurred and I had to go and have a lie-down in a darkened room for a time!
GG
Groundgripper is online now   Reply
Old 9th Oct 2006, 09:09   #11 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
mdc - yes I noticed the website had code for calculating a track, but it's in VB/NET script and I do not posses the technical knowledge nor the programs to use it!
Morning Crescent - That's the formula i'm looking for, but it's just a case of converting it to an Excel formulae
Groundgripper - no problem, thanks for your help with the distance formula!
Oxeagle is offline   Reply
Old 9th Oct 2006, 13:16   #12 (permalink)
 
Join Date: Dec 2005
Location: UK Midlands
Posts: 140
OA

Check your PMs

MC
Mornington Crescent is offline   Reply
Old 18th Oct 2006, 15:24   #13 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
Ok, so i've managed to put together a distance and bearing calculator with extremely valuable help and advice from you guys, so many thanks However, I have now hit another problem. Originally when I had completed it, the bearing calculator was working perfect give or take half a degree or so, but if the track crossed the meridian the distance calculator strated spewing random numbers at me. Managed to correct that, but now the track headings go out the window when the track crosses the meridian!

Anyone have any ideas? I have tried everything I can think of and more over the last week, i've checked every bit of coding 3 of 4 times, and I still can't work it out. It's enough to make you loose the will to live!

If anyone could shed any light on this problem, I would be extremely grateful

Cheers,

Ox
Oxeagle is offline   Reply
Old 18th Oct 2006, 17:17   #14 (permalink)
 
Join Date: Jul 2005
Location: LFBO
Posts: 77
Aviation Formulary V1.43

This should keep you happy!

http://williams.best.vwh.net/avform.htm
Been Accounting is offline   Reply
Old 20th Oct 2006, 15:00   #15 (permalink)
 
Join Date: Feb 2006
Location: Here and there
Posts: 215
Finally figured out what was going wrong! I now have a fully working distance and track calculator. Many thanks guys, couldn't have done it without the help I recieved for you lot!

Ox
Oxeagle is offline   Reply
Reply
 
 
 


Thread Tools


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT. The time now is 18:59.


vBulletin® v3.8.7, Copyright ©2000-2014, vBulletin Solutions, Inc.
SEO by vBSEO 3.6.1
1996-2012 The Professional Pilots Rumour Network