Eastings & Northings

[Michael]

Hi All
I have about 70 sets of Easting & Northing Co-Ordinates
I want to be able to use one set as my base Co-Ords, in
say A1 & B1.Then when I type in another set in say A2 &
B2, I would like to find the distance and angle
Any help would be appreciated

Regards
Michael

 

[JohnT]

Have a look at

http://www.cpearson.com/excel/latlong.htm

This covers some stuff on Lat/Long and Great Circle calculations

Regards
Joh

 

[Vaughan]

Takes me back to O Level Maths. Is that what this is for?

By Pythagoras, the distance P1P2 can be calculated as :

=SQRT((A2-A1)^2+(B2-B1)^2)

The angle is more tricky. I need to play for a bit.

 

[James Silverton]

OK, well my maths is letting me down a bit, but you will be looking

to find the arctangent of (A2-A1)/(B2-B1) and then interpreting the
result as a bearing.

In excel the arctangent can be found using the ATAN() function,

giving you a result in radians. To turn it into degrees try:

=DEGREES(ATAN((A2-A1)/(B2-B1)))

This then needs to be turned into a bearing from North I guess. I

don't know if there is a clever way, but basically:

if A2 > A1 and B2 > B1 the bearing is between 0 and 90 degrees
if A2 > A1 and B2 < B1 the bearing is between 90 and 180 degrees
if A2 < A1 and B2 < B1 the bearing is between 180 and 270 degrees
if A2 < A1 and B2 > B1 the bearing is between 270 and 360 degrees

... so you could create a formula that would test for these

conditions and add the appropriate number of quadrants.

Note also that if B1 = B2, the above will returna #DIV/0! error, so

you will have to test for that too.

Sorry, don't have the energy to try to put all that into a formula, but maybe it will help you.

Have a look at ATAN2 using (En-E0), (Nn-N0) assuming (E0, N0) is your
origin point.

Best wishes,

 

[Michael]

Thanks guys
You're right, it is "O" level Maths. Trouble is I did that
30 years ago.
And you have refreshed my memory.
Thanks for the help

Regards
Michael

-----Original Message-----
OK, well my maths is letting me down a bit, but you will

be looking to find the arctangent of (A2-A1)/(B2-B1) and
then interpreting the result as a bearing.

In excel the arctangent can be found using the ATAN()

function, giving you a result in radians. To turn it into
degrees try:

=DEGREES(ATAN((A2-A1)/(B2-B1)))

This then needs to be turned into a bearing from North I

guess. I don't know if there is a clever way, but
basically:

if A2 > A1 and B2 > B1 the bearing is between 0 and 90 degrees
if A2 > A1 and B2 < B1 the bearing is between 90 and 180 degrees
if A2 < A1 and B2 < B1 the bearing is between 180 and 270 degrees
if A2 < A1 and B2 > B1 the bearing is between 270 and 360 degrees

.... so you could create a formula that would test for

these conditions and add the appropriate number of
quadrants.

Note also that if B1 = B2, the above will returna #DIV/0!

error, so you will have to test for that too.

Sorry, don't have the energy to try to put all that into

a formula, but maybe it will help you.