×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Converting N/E Coordinates to Azimuth

Converting N/E Coordinates to Azimuth

Converting N/E Coordinates to Azimuth

(OP)
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. smile

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

Fred

RE: Converting N/E Coordinates to Azimuth

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

TTFN



RE: Converting N/E Coordinates to Azimuth

(OP)
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

RE: Converting N/E Coordinates to Azimuth

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

RE: Converting N/E Coordinates to Azimuth

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.

RE: Converting N/E Coordinates to Azimuth

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

RE: Converting N/E Coordinates to Azimuth

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: www.essexsteam.co.uk

RE: Converting N/E Coordinates to Azimuth

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

RE: Converting N/E Coordinates to Azimuth

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.

RE: Converting N/E Coordinates to Azimuth

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?  

RE: Converting N/E Coordinates to Azimuth

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



RE: Converting N/E Coordinates to Azimuth

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

RE: Converting N/E Coordinates to Azimuth

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?

RE: Converting N/E Coordinates to Azimuth

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: www.essexsteam.co.uk

RE: Converting N/E Coordinates to Azimuth

(OP)
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.  smile

RE: Converting N/E Coordinates to Azimuth

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?

RE: Converting N/E Coordinates to Azimuth

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



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources