+ Reply to Thread
Results 1 to 7 of 7

Setting Out Sheet - Rectangular to Polar

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question Setting Out Sheet - Rectangular to Polar

    Hi All,

    In another non-sports related sheet (which makes a change for me), I was at a training course this week and a colleague mentioned some work I'd done (didn't realise that I'd done it though) and that they spotted a substantial typo in it (one figure out of about 160 - and it was out enough that it was obviously wrong, 15 miles wrong on a 200m long site). In the course of the training, I spotted a manual way of adding a check to whenever I do figures like that in future (we don't have the IT to run the software that we have that would remove the need for me to manually input or copy and paste figures in).

    In short I have put together a rough example sheet (as once I have the formulas right, I can then apply it to a bigger sheet, do a data validation pick table etc.).

    In the attached (which is a two station, 4 point example - the job I made a typo on was about 70 points and 10 stations), my site co-ordinates are in yellow in columns B and C, the distance (to A in this case) in red in D and E is good (which is all I really want to add as a check) - but whilst I was at it, I figured I'd do the bearing as well. The working columns, F to K in green are good (I think, I may have to re-work the quadrant (Q) adjust, I have some papers on my desk - I'm not a surveyor by background).

    The column that I need to re-work is L (in blue).

    Basically I want to pull whichever check comes up between H and K. For some reason I have L6 and L7 working, but L4 and L5 don't (which makes me think my formula is off (I went with an index, iferror and match?).

    I'm sure I can probably tidy the whole sheet up in the long term, but for now I just want to get something workable.

    Thanks in advance for any suggestions,

    Trig_Problem_Forum Copy.xlsx
    Last edited by mrvp; 09-27-2014 at 05:32 PM. Reason: accidental post - without attachment and tags

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Setting Out Sheet - Rectangular to Polar

    You need a 4-quadrant arctangent for bearing. For compass heading (CW from north), E4 and down should be

    =MOD(DEGREES(ATAN2(C$2-C4, B$2-B4)), 360)

    A
    B
    C
    D
    E
    1
    x
    y
    Distance To A
    Bearing To A
    2
    Stn A
    130.000
    120.000
    3
    Stn B
    140.000
    120.000
    4
    Pt 1
    128.753
    122.180
    2.51
    150.2
    5
    Pt 2
    129.539
    127.118
    7.13
    176.3
    6
    Pt 3
    136.946
    125.940
    9.14
    229.5
    7
    Pt 4
    136.160
    121.002
    6.24
    260.8
    Last edited by shg; 09-27-2014 at 06:13 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Setting Out Sheet - Rectangular to Polar

    Quote Originally Posted by shg View Post
    You need a 4-quadrant arctangent for bearing. For compass heading (CW from north), E4 and down should be

    =MOD(DEGREES(ATAN2(C$2-C4, B$2-B4)), 360)
    Hi - maybe it's me where I am not a surveyor by background (I'm an engineer) and maybe I misrepresented the question (maybe I have misunderstood what I am trying to do), but I know my column E is good (I'm following the worked example from the course). It's just the adjustment that I need to add to column E to make it relative (to the setting out station and the points) e.g. point 4 will be to the right (east of station A) and just barely above it (to the north of station A), therefore it would be approximately 80 degrees from A (if I'm making sense) - assuming up is north (bad way of putting it but you know what I mean). I'm going to look at this and come back as I think it's me that may have misunderstood (or possibly mislabelled something) and I know you are usually on point.

  4. #4
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Setting Out Sheet - Rectangular to Polar

    Hi,

    Just following up on this; if you take your formula

    =MOD(DEGREES(ATAN2(C$2-C4, B$2-B4)), 360)

    It removes the need for my column D (in what I attached), and then if you take what's in your column E above and take 180 from it you have what I originally had in column F. So I could definitely take a column out using your formula and I am getting the right angle (if I add a -180), but still need to add an adjustment to it to make it relative to the points on the ground (if I am making sense?).

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Setting Out Sheet - Rectangular to Polar

    Quote Originally Posted by mrvp View Post
    ... therefore it would be approximately 80 degrees from A (if I'm making sense) - assuming up is north
    Right -- but the column heading says bearing TO A, not FROM A. If you want FROM,

    =MOD(DEGREES(ATAN2(C4-C$2, B4-B$2)), 360)

  6. #6
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Setting Out Sheet - Rectangular to Polar

    Quote Originally Posted by mrvp View Post
    I'm going to look at this and come back as I think it's me that may have misunderstood (or possibly mislabelled something) and I know you are usually on point.
    Quote Originally Posted by shg View Post
    Right -- but the column heading says bearing TO A, not FROM A. If you want FROM,

    =MOD(DEGREES(ATAN2(C4-C$2, B4-B$2)), 360)
    Spot on - (but at least I kind of spotted my problem ). Thanks for taking the time to look at this. From talking with the lecturer that was doing the training in the week, when I talked the idea through I was given the impression that I would need multiple working columns (hence the reason why I was trying to take the approach that I did). I'll re-attach my re-work to this post and mark it solved in a minute, as I found a nice degrees minutes second conversion.

    Trig_Problem_Forum Copy_RevA.xlsx
    Last edited by mrvp; 09-28-2014 at 01:51 PM. Reason: added my re-work in following shg's advice

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Setting Out Sheet - Rectangular to Polar

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Insert rectangular autoshape using vba based on different cell value
    By hifliers in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2014, 02:59 AM
  2. Polar curve plotted in polar chart
    By Rocky2013 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-05-2013, 10:45 AM
  3. Find the position of a value within a rectangular range
    By Larry.LeBlanc@O in forum Excel General
    Replies: 6
    Last Post: 10-27-2011, 06:21 PM
  4. Replies: 1
    Last Post: 06-25-2006, 01:10 PM
  5. [SOLVED] Splash screen that is not rectangular?
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2006, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1