Calculating Bearings With Two Sets of Lat/Long Coordinates

[edt4444]

I have a list of locations with corresponding lat/long coordinates and am trying to calculate initial bearings between two locations. The formula I am using is:

=IF(ROUND(DEGREES(ATAN2(COS(RADIANS($I2))*SIN(RADIANS($K2))-SIN(RADIANS($I2))*COS(RADIANS($K2))*IF($J2=$H2,$H2,COS(RADIANS($J2-$H2))),IF($J2=$H2,$H2,SIN(RADIANS($J2-$H2)))*COS(RADIANS($K2)))),2)<0,ROUND(DEGREES(ATAN2(COS(RADIANS($I2))*SIN(RADIANS($K2))-SIN(RADIANS($I2))*COS(RADIANS($K2))*IF($J2=$H2,$H2,COS(RADIANS($J2-$H2))),IF($J2=$H2,$H2,SIN(RADIANS($J2-$H2)))*COS(RADIANS($K2)))),2)*-1,ROUND(DEGREES(ATAN2(COS(RADIANS($I2))*SIN(RADIANS($K2))-SIN(RADIANS($I2))*COS(RADIANS($K2))*IF($J2=$H2,$H2,COS(RADIANS($J2-$H2))),IF($J2=$H2,$H2,SIN(RADIANS($J2-$H2)))*COS(RADIANS($K2)))),2))

Column I = Latitude 1, Column H = Longitude 1, Column K = Latitude 2, Column J = Longitude 2

It seems to work in some instances and in other cases does not. For example, when using these coordinates: Lat/Long1 = -86.879036, 33.525203 and Lat/Long2 = -86.1852, 39.70122 it seems the bearing (4.95, N) is correct. However, when using: Lat/Long1 = -86.879036, 33.525203 and Lat/Long2 = -89.864998, 35.01351 the bearing (58.07, ENE) is incorrect.

Any ideas as to why this formula seems to work sometimes and at other times not?

 

[RickXL]

Hi,

I have not looked at the formula, just the data.

-89.864998 degrees is quite close to the South Pole. If it ever reaches -90 degrees then every direction will be North.

 

[StephenCrump]

Where did you get your formula?

Based on some simple examples, it doesn't seem to produce reasonable results.

Based on a Dr. Math formula: Math Forum - Ask Dr. Math

I have coded an alternative:

You E2: =IF(ROUND(DEGREES(ATAN2(COS(RADIANS(B2))*SIN(RADIANS(D2))-SIN(RADIANS(B2))*COS(RADIANS(D2))*IF(C2=A2,A2,COS(RADIANS(C2-A2))),IF(C2=A2,A2,SIN(RADIANS(C2-A2)))*COS(RADIANS(D2)))),2)<0,ROUND(DEGREES(ATAN2(COS(RADIANS(B2))*SIN(RADIANS(D2))-SIN(RADIANS(B2))*COS(RADIANS(D2))*IF(C2=A2,A2,COS(RADIANS(C2-A2))),IF(C2=A2,A2,SIN(RADIANS(C2-A2)))*COS(RADIANS(D2)))),2)*-1,ROUND(DEGREES(ATAN2(COS(RADIANS(B2))*SIN(RADIANS(D2))-SIN(RADIANS(B2))*COS(RADIANS(D2))*IF(C2=A2,A2,COS(RADIANS(C2-A2))),IF(C2=A2,A2,SIN(RADIANS(C2-A2)))*COS(RADIANS(D2)))),2))

Me F2: =MOD(ATAN2(COS(RADIANS(A2))*SIN(RADIANS(C2))-SIN(RADIANS(A2))*COS(RADIANS(C2))*COS(RADIANS(D2-B2)),COS(RADIANS(C2))*SIN(RADIANS(D2-B2)))*180/PI(),360)

I haven't tested exhaustively and can't guarantee that the formula is correct, but the results at least seem reasonable?
PS - I get 31.4 and 179.9 degrees respectively for your two examples.

*	A	B	C	D	E	F

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]LAT1[/TD]
[TD="align: right"]LON1[/TD]
[TD="align: right"]LAT2[/TD]
[TD="align: right"]LON2[/TD]
[TD="align: right"]You[/TD]
[TD="align: right"]Me[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90.0[/TD]
[TD="align: right"]0.0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90.0[/TD]
[TD="align: right"]180.0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]85.7[/TD]
[TD="align: right"]0.0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]94.3[/TD]
[TD="align: right"]0.0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]84.3[/TD]
[TD="align: right"]5.4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]95.7[/TD]
[TD="align: right"]354.6[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[StephenCrump]

Oops, I think I've incorrectly transposed some Lat/Lon numbers using your formula, so some of the "your" results are mis-stated above.

But my comments still stand.

 

[edt4444]

Stephen, I found the formula on another site. It never gave me consistent results, however the formula you have shared seems to be working perfectly! I tested with several sets of coordinates and seem to be getting accurate results.

Thank you very much for your help!

 

[StephenCrump]

You're welcome.

There are no guarantees with formulae you find on the web (including mine!) but I have just cross checked some of my results against the calculator here:

Calculate distance and bearing between two Latitude/Longitude points using haversine formula in JavaScript

and the initial bearings match to degrees, mins and secs.

 

[edt4444]

I used the same calculator to cross reference!

 

[StephenCrump]

Proof by internet. Could there be anything more robust?