Excel Lat & Long calculations in Excel

Joined
Jan 21, 2017
Messages
16
Reaction score
5
I'd like to find know how to calculate the compass bearing between two points defined by latitude and longitude. This would be over short distances so a simple rhumb line calculation, not a great circle route. Can anyone please help me with this?
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thank you Evan, but I'm afraid that's way too technical for me! I'm trying to find a formula or series of formulae so that I can enter a starting Lat/Long and destination Lat/Long and obtain a rhumb line compass bearing from start to destination. If the answer is on that page I'm afraid I'm not sufficiently knowledgeable to see it.
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thank you. I've been finding such sites generally beyond my understanding but igismap looks more helpful. I'll give it a go and let you know
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thank you all for your suggestions. Unfortunately I couldn't get any of these to work, but I'll keep looking
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thanks Becky. It's exactly what I've been looking for ... except I can't get it to work! Has anyone else tried it?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
I can't figure it out - it has been a long time since I studied trigonometry! I presume that the underscores should be something else - maybe minus or division signs? It would also depend on what format the answer needs to be in...
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Me too - I knew I should have paid more attention! I've been trying some substitutions for the underlines but so far without success. Also, is it valid to start the formula with \= ?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Got it figured :D

The co-ordinates need to be converted into radians first, because that's what format Excel expects the data to be in.

To convert your co-ordinates to radians multiply by Pi (in Excel you can use PI() ) and divide by 180. Do this for all of the co-ordinates, and then name these as LatA, LonA etc as before.

Ignore that first formula, use this one instead:

=MOD(ATAN2((COS(LatA)*SIN(LatB))-(SIN(LatA)*COS(LatB)*COS(LonB-LonA)), SIN(LonB-LonA)*COS(LatB)),2*PI())*180/PI()

The "*180/PI()" at the end converts the bearing from radians back to degrees.

Give it a try and let me know if it works for you!
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
I reckon you've cracked it! The only further thing I found I needed to do was to subtract the solution from 360 so I'm using:

=360-(MOD(ATAN2((COS(LatA)*SIN(LatB))-(SIN(LatA)*COS(LatB)*COS(LonB-LonA)), SIN(LonB-LonA)*COS(LatB)),2*PI())*180/PI())

I've now worked countless examples and have not yet found any anomalies. Thanks again for your help.
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thanks for previous replies. My calculations of bearing between two points work perfectly using the formula confirmed in my reply to Becky on Feb 1. However, I've also been trying to find a formula to determine the destination Lat/Long using bearing and distance from a given starting point. I'm converting the coordinates to radians and assume my distance needs to be in kilometres, but nothing I've found seems to give correct results. Can anyone help me with this, please?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
This should calculate the distance between the two locations:

=ACOS((COS(LatA)*COS(LatB)*COS((-1*LonB)-(-1*LonA)))+(SIN(LatA)*SIN(LatB)))*r

'r' is the radius of the Earth, so if you want the result in km then use 6,371km.

:)
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thanks Becky, that's really useful but not what I'm trying to do. If I know the starting point Lat/Long, the bearing to be traveled and the distance to be travelled, I want to be able to calculate the Lat/Long of the destination.

Unfortunately the formulae I've found so far are giving me wildly incorrect results.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Joined
Jan 21, 2017
Messages
16
Reaction score
5
Thank you Becky & Evan. This is the formula I'd been trying to use without success, but I've just found where I was going wrong. I'd got in a mess with my radians - but I think I've got it right now.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Glad to hear it :) I haven't tried it myself but let me know if you want me to take a look.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top