Calculate LAT/LON when given start LAT/LON, azimuth and distance

hamiltow

New Member
Joined
Aug 30, 2009
Messages
11
I need to calculate the end LAT/LON for thousands of lines of data. The existing spreadsheet has the start LAT, start LON, azimuth and distance. Is there a formula so excel can calculate it for me?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,912
Office Version
365
Platform
Windows
Have a look here
http://www.movable-type.co.uk/scripts/latlong.html


d being the distance travelled, R the earth’s radius

Excel: (all anglesin 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))
* Remember that Excel reverses the arguments to ATAN2 – see notes below

For final bearing, simply take the initial bearing from the end point to the start point and reverse it with (brng+180)%360.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,912
Office Version
365
Platform
Windows
Fraid I can't help you.
It's a long, long time since I used trig functions.
 

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
Hi hamiltow;

Are the following results OK for you?

Lat2: 33.3247617235927

Lon2: -82.1798292854672
 
Last edited:

hamiltow

New Member
Joined
Aug 30, 2009
Messages
11
Hi hamiltow;

Are the following results OK for you?

Lat2: 33.3247617235927

Lon2: -82.1798292854672

Thanks for the assistance. It looks like the 2nd positions are only about 3084 meters from the first point at a bearing of 114.5 degrees.
 

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
According to my calculations, the travel distance for the second point is: 5.00000047512941 km.
 

hamiltow

New Member
Joined
Aug 30, 2009
Messages
11
I plotted both sets of coordinates and they come up 3.081 km apart with a bearing of 114.4933 degrees.
 

hamiltow

New Member
Joined
Aug 30, 2009
Messages
11
Using the online tool I got:

Latitude: 33°17′56″N 33.29886611
Longitude: 82°10′48″W -82.17990242
Final bearing: 145°54′59″ 145.91653196
Back bearing: 325°55′00″ 325.91653196

Those figures check out when they are plotted.

It looks like the LON2 of the calculation you did was correct but the LAT2 wasn't.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,013
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top