Calculating Distance & Bearing from Lat Lon Coordinates

[CPLangmaid]

Hi All,

I have a spreadsheet with a long list of Lat & Lon coordinates which I need to calculate the distance and bearing from a base point. I have tried many formulas, some from this site and some from other sites, but nothing seems to work. I don't know if it is the format of the coordinates or the method of input of cell references that is causing the error, but nothing seems to work for me. Coordinates are entered as Degrees, Minutes, Decimal Minutes (DD,MM.MMM) I'm not sure if this format needs to be converted, and whether I can use North/South & East & West or if I need to format with negative numbers for West & South? Distances are not that great, <25 Nautical Miles so Rhumb Line calculations are acceptable rather than Great Circle.

Below is a simple sample of the spreadsheet and data. Formulas need to be in D3 & E3 downwards. All calculations are from the base point on row 2.

Any guidance would be much appreciated.

Many thanks.
Chris


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]LAT[/TD]
[TD]LON[/TD]
[TD]DISTANCE
NM[/TD]
[TD]BEARING
Deg[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BASEPOINT[/TD]
[TD]50 ° 48 '.400N[/TD]
[TD]00 ° 06 '.300W[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LOCATION 1[/TD]
[TD]50 ° 44 '.714N[/TD]
[TD]00 ° 17 '.098E[/TD]
[TD]8.02[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]LOCATION 2[/TD]
[TD]50 ° 32 '.136N[/TD]
[TD]00 ° 38 '.331W[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LOCATION 3[/TD]
[TD]50 ° 44 '.569N[/TD]
[TD]00 ° 00 '.741[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

This link may be of some help: https://analystcave.com/excel-calculate-distances-between-addresses/

 

[CPLangmaid]

Hi Mumps,

Thanks for your reply. The problem I have with this method is that the required format for the Lat & Lon is Eastings & Northings (41.43216,-81.49992) rather than degrees, Minutes decimal minutes (50 ° 44 '.714N ; 00 ° 17 '.098E) and I'm not sure how to work around this problem.

Thanks
C