Converting N/E Coordinates to Azimuth 1

[fel3]

I'm a civil engineer, and was playing around with Excel this afternoon when I had a wild hare to come up with a more clever way to calculate the azimuth of a line than I had been using.

Trig by itself won't do it. Angle=ATAN(DeltaE/DeltaN) is the starting point, but it results in dividing by zero at 0° and 180°. Also, the other results are limited to quadrants in a mirror image fashion, while azimuths are clockwise 0°-360° starting from north. It's doable, but the method gets pretty complicated.

I found the answer by searching the Excel help file for "angle." I have a love/hate relationship with Excel help, but this time it came through.

The solution is to convert DeltaN and DeltaE to a complex number, then use the IMARGUMENT function to convert the complex number to an angle. Here's what I came up with:

Azimuth = MOD(360+DEGREES(IMARGUMENT(COMPLEX(Northing2-Northing1,Easting2-Easting1))),360)

Assuming I typed this correctly, this will give the azimuth in decimal degrees. From here you can use the time functions convert this to DD-MM-SS. I've done this before, but the technique is at the office, I'm at home, and I'm too tired to do it right now.

Anyway, please tell me what you think. I would also be interested in other techniques you have used.

Fred

 

[IRstuff]

Excel already has a function that eliminates the singularity: ATAN2(x,y)

TTFN

 

[fel3]

IRstuff…

Well, that's certainly better than what I came up with. Thanks for jumping in. In all the years I have used Excel, I somehow totally missed ATAN2. Now the equation looks like this:

Azimuth = MOD(360+DEGREES(ATAN2((B5-B4),(C5-C4))),360)

Fred

 

[mamaw]

I'm new to the azimuth, I'm just trying to learn my husband's business, I know there must be a way to write a program for azimuths, backsights, and forward sights, with the magnetic, and true bearings.
Can you help me?
Thanks

 

[Catcher]

Atan2 is pretty cool but I have always had a problem calculating the azimuth because excel seems to give the angle from east or west and to get it to work from north is quite complicated. Does anyone have an idea how to calc the angle always from north ?
Thanks guys you are always a big help to me.

 

[chicopee]

To FEL3-I am somewhat puzzle about the use of the MOD function which when used with excel returns a remainder from a division of terms conducted within the parentheses. How do you figure this remainder to be an azimuth value from the North or South

 

[johnwm]

chicopee,
The equation shown:
Azimuth = MOD(360+DEGREES(ATAN2((B5-B4),(C5-C4))),360)
simply adds 360 to the DEGREES calc, thus ensuring that it's value is positive, then shows that value MOD 360. You are then assured of a positive value >=0 and <360

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[Catcher]

Thanks johnwm will give it a try and hopefully figure it out

 

[TerryScan]

If one is going to do a lot of coordinate geometry in a spreadsheet, one may interested in checking out:

http://www.abykus.com/.

I recently played with it a bit.
It has pretty powerful COGO and matrix operations built-in. On the downside, it does not appear to be compatible with other spreadsheet programs.

 

[Catcher]

it's true that the equation Azimuth = MOD(360+DEGREES(ATAN2((B5-B4),(C5-C4))),360)gives a positive result but i have a line with an azimuth of 36 deg from north but the equation gives me 54 deg my question is is there a way i can always get the bearing from the north in whichever quadrant the azimuth is?

 

[IRstuff]

What are your inputs? The equation is rather limited in scope, so the only explanation would be that you've flipped N and E, resulting in finding the arccotangent instead of the arctangent.

TTFN

 

[Catcher]

Thanks IRSTUFF as you said i was flipping N and E now it works fine in all the quadrants.
Thanks again

 

[chicopee]

SORRY JOHNWM,BUT WHEN I INPUT THIS EXAMPLE MOD(456,360), MY RESULT ON MY MS EXCEL IS 96 WHICH IS THE REMAINDER OF 456/360-- SO WHAT AM I MISSING WITH THE EQUATION YOU PRESENTED ME FOR AN AZIMUTH VALUE?

 

[johnwm]

That's what MOD does - it gives the remainder from an integer division. As I said earlier the azimuth should finish with a value 0 <= x < 360 which is what the formula achieves.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[fel3]

Looks like my thread has been in good hands while I was out.

Fred

P.S. It's the trig functions that make engineering far more interesting than accounting.

 

[chicopee]

I hate to beat this dead horse JOHWM, but lets say I have -36.00d below the east line, then when added to 360.00d I get 456.00d which when divided by 360.00d give me a remainder of 96 which is NOT the azimuth angle from the North or South. So what am I missing with that MOD function to give you an azimuth value?

 

[IRstuff]

Something else is wrong, then. You cannot get 456 with 36 and 360 as arithmetic inputs, only 324 or 396.

Perhaps you should paste the exact formula as you are using it in your spreadsheet.

TTFN