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

#### hamiltow

##### New Member
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?

### 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
Have a look here
http://www.movable-type.co.uk/scripts/latlong.html

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

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
It's a long, long time since I used trig functions.

#### Haluk

##### Rules Violation
Hi hamiltow;

Are the following results OK for you?

Lat2: 33.3247617235927

Lon2: -82.1798292854672

Last edited:

#### hamiltow

##### New Member
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
According to my calculations, the travel distance for the second point is: 5.00000047512941 km.

#### hamiltow

##### New Member
I plotted both sets of coordinates and they come up 3.081 km apart with a bearing of 114.4933 degrees.

#### hamiltow

##### New Member
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.