Excel Lat & Long calculations in Excel

[Richell Yonson]

Hello!

I keep getting wrong answers using the above formulas. I have the following details;

Lat1 = 10.296570
Lon1 = 123.903479
Brng = N 15deg 16min W
Distance = 4743.09 m

Can you help me figure out the step by step solution in getting the destination point?

Thank you in advance.

(e-mail address removed)

 

[Tim Rowley]

I'm not an expert in the maths but I'll try to help as the formulae work very accurately for me in a yacht race planning spreadsheet that I've put together. Firstly, how are your Lat1 and Lon1 specified? Are these numbers decimal degrees or have you converted them into radians? Secondly, I don't understand your bearing. I would expect to see a number 0-359 without N, S, E or W.

 

[Richell Yonson]

I'm not an expert in the maths but I'll try to help as the formulae work very accurately for me in a yacht race planning spreadsheet that I've put together. Firstly, how are your Lat1 and Lon1 specified? Are these numbers decimal degrees or have you converted them into radians? Secondly, I don't understand your bearing. I would expect to see a number 0-359 without N, S, E or W.

Hi Tim!

Lat & Long are in decimal degrees and still not converted to radians. Bearings are based on Azimuth, I believe if you convert it to decimal it’s equivalent to 344.73.

Quick background: Here in the Philippines, our Land Title are indicated with Tie Point (starting point) and Bearing and Distance. I’d like to know the destination point (Lat Long) so I can plot it on google earth and see how accurate the location of the property is. [See attached file for example]

Thanks for the response.

 

[Tim Rowley]

Hi Tim!

Lat & Long are in decimal degrees and still not converted to radians. Bearings are based on Azimuth, I believe if you convert it to decimal it’s equivalent to 344.73.

Quick background: Here in the Philippines, our Land Title are indicated with Tie Point (starting point) and Bearing and Distance. I’d like to know the destination point (Lat Long) so I can plot it on google earth and see how accurate the location of the property is. [See attached file for example]

Thanks for the response.

The formula I'm using is the one given previously in this thread:

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

I believe that you need to convert your lat/long into radians and then convert the solution back, the bearing needs to be 0-359 and you need the value of R in the same units as d. I work in nautical miles but I guess you want metres. Also, all my longitudes are W and I don't know if anything needs to change for an E position. I think that's about all I can suggest.