Excel Lat & Long calculations in Excel

[Tim Rowley]

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?

 

[EvanDavis]

http://www.movable-type.co.uk/scripts/latlong.html

 

[Tim Rowley]

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.

 

[Taffycat]

Hi Tim - welcome to PC Review.

Would this one help? http://www.sunearthtools.com/tools/distance.php http://www.sunearthtools.com/tools/distance.php

Or maybe this: http://www.igismap.com/formula-to-f...-angle-between-two-points-latitude-longitude/

 

[Tim Rowley]

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

 

[muckshifter]

I found one ...

http://www.movable-type.co.uk/scripts/latlong.html

 

[Tim Rowley]

Thank you all for your suggestions. Unfortunately I couldn't get any of these to work, but I'll keep looking

 

[Becky]

I think this might be what you're looking for - it's a step-by-step explanation of what to do:

https://www.techwalla.com/articles/use-excel-calculate-bearing-between-two-points

 

[Tim Rowley]

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]

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

 

[Tim Rowley]

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]

Got it figured

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!

 

[Tim Rowley]

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.

 

[Becky]

You're welcome

 

[Tim Rowley]

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]

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.

 

[Tim Rowley]

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]

The answer is in the first link that @EvanDavis posted above

http://www.movable-type.co.uk/scripts/latlong.html

Scroll down to "Destination point given distance and bearing from start point"

Excel: (all angles in radians)
lat2: = ASIN(SIN(lat1)*COS(d/R) + COS(lat1)*SIN(d/R)*COS(brng))
lon2: = lon1 + ATAN2(COS(d/R)-SIN(lat1)*SIN(lat2), SIN(brng)*SIN(d/R)*COS(lat1))

d = distance travelled, r = radius of Earth, brng = bearing

 

[Tim Rowley]

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]

Glad to hear it I haven't tried it myself but let me know if you want me to take a look.