+ Reply to Thread
Results 1 to 10 of 10

find the X Y coordinate using length and distance.

  1. #1
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    find the X Y coordinate using length and distance.

    Dear Sir,
    The column B and C are showings line of coordinate. we find coordinates around the point based on length (Column F) and distance (Column G) showing in excel.
    See attached files to know better

    Thank in advance..

    Results should be from the formula OR vba function.
    Attached Files Attached Files
    Last edited by sanju2323; 02-23-2019 at 12:19 PM.

  2. #2
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: find the X Y coordinate using length and distance.

    Maybe :
    Please Login or Register  to view this content.
    Result is on Sheet1!Y3:Z34

  3. #3
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: find the X Y coordinate using length and distance.

    Thank you very much sir,
    Your code is working very well, but some place is wrong. I have tested it with two more files, in the "Test2" file, it shows two wrong answers, I highlighted in the spreadsheet and your code was not processed in the second file (Test3).
    If possible, give the code in the VBA function.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: find the X Y coordinate using length and distance.

    I thought that the line orientation between two consecutive nodes will always located on quadrant I (just like the data on your first example file), never thought that your data (on the other files which I didn't see before) can be located on all quadrants (I to IV). Here is the amended code :

    Please Login or Register  to view this content.

    Now for first file (on post #1) and "Test2.xls" file, it will show the same result with your manual calculation.


    As for file "Test3.xls" file, there are 3 nodes different with your manual calculation, they are :
    - SL 9 (at row 11)
    - SL 37 (at row 39)
    - SL 93 (at row 95)
    All these 3 nodes are located exactly on base nodes at table Sheet1!A:C (not interpolated liked other nodes), for example :
    - SL 9 (at row 11) of right table, is exactly at SL 6 (at row 8) of left table
    - SL 37 (at row 39) of right table, is exactly at SL 24 (at row 26) of left table
    - SL 93 (at row 95) of right table, is exactly at SL 47 (at row 49) of left table
    and I don't know your manual formula that yields these 3 nodes (I have searched for hours, and still don't know what formula you are using for them).


    Quote Originally Posted by sanju2323 View Post
    If possible, give the code in the VBA function.
    No, it is too complex and will slow down the calculation very much.

  5. #5
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: find the X Y coordinate using length and distance.

    Hi Sir,
    I got this coordinate from AutoCAD software manually, it was a long process, but due to your code it is going to be a great time saver.
    I think there is a small error in the coordinates due to the use of different software. But no matter then it is enough for me, anyway you have helped me a lot. I am very happy with this. Thank you..
    Have a good day
    Last edited by sanju2323; 02-24-2019 at 08:16 AM.

  6. #6
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: find the X Y coordinate using length and distance.

    I think I found a formula that is used by AutoCAD for those nodes that located exactly at a based node.
    They are not using the line slope between previous node and current node [P(i-1) -> P(i)], or line slope between current node and next node [P(i) -> P(i+1)], but rather, they use the line slope between previous node and next node [P(i-1) -> P(i+1)], which is actually a strange approach.

    By using this formula, for those nodes that located exactly at a based node, the difference between AutoCAD and mine result becomes drastically smaller.

    For example, for file "Test3.xls", for SL 37 (at row 39) :
    - using AutoCAD (your manual result) : 53.16 , -35.445
    - using previous method (sub Test2) : 50.2908 , -35.5304
    - using new method (sub Test3) : 53.2982 , -35.4651
    so the improvement is (53.16 - 50.2908) / (53.16 - 53.2982) = 2,076 %

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: find the X Y coordinate using length and distance.

    Hi Sir,

    I think the point coordinates are changing from where the direction of the line is changing. But this time your code is much better.
    Thank you....
    Attached Images Attached Images

  8. #8
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: find the X Y coordinate using length and distance.

    Yes, that is what actually I want to tell you.

    If the point is located between two base nodes, for example if the point is located on half way between point 23 and 24, than there is no problem at all, this point can use the slope line (angle) of line 23-24.

    But if the point is located exactly at the same place with a base nodes, for example if the point is located at point 24, then AutoCAD will give result that is quite strange number. AutoCAD is not using angle of line 23-24, neither using angle of line 24-25 (either one is legal, because point 24 is part of line 23-24, and also part of line 24-25, so technically if we use one of them, it is technically correct). But rather, AutoCAD is seemed to use the slope line (imaginary line) of point 23-25 (skipping point 24, the one we are looking for).

    And by using this method, the result is practically now same between AutoCAD result and my sub (as I described in my previous post, the sameness is increased by 2000%).

    =====================

    After all, I've been pleased now with the result using this method, so no further enhancement will be made.

    Thanks for marking the thread as solved.


    Regards

  9. #9
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: find the X Y coordinate using length and distance.

    Hi Sir, I know that this code has been worked hard to make, It was not an easy task I am also surprised that this is possible. It's just an expert can do. And after looking at points number 9,37,47 in AutoCAD, that coordinate is not a problem.
    Thank you for making this code.
    Last edited by sanju2323; 02-25-2019 at 10:31 AM.

  10. #10
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: find the X Y coordinate using length and distance.

    Hi Sir,
    can this topic be reversed, the length and distance can be detected from the line and point coordinates. I already have an excel file that can solve the issue but it is a difficult one. It can only know one line from coordinates. This is another way to find out from several coordinates of the line.
    Attached Files Attached Files
    Last edited by sanju2323; 02-28-2019 at 12:09 AM.

+ 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. Find distance to distance of addresses in excel
    By pick44 in forum Excel General
    Replies: 5
    Last Post: 02-08-2019, 10:05 AM
  2. Dist calculation
    By sanju2323 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2016, 11:40 AM
  3. Replies: 5
    Last Post: 10-02-2015, 05:38 PM
  4. [SOLVED] Need a formula to find the value listed at an X Y coordinate
    By BeachRock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2014, 02:04 PM
  5. User input to get coordinate distance
    By pitakoc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-01-2012, 11:20 AM
  6. Replies: 0
    Last Post: 10-16-2012, 04:27 AM
  7. Find (x,y) coordinate, given target Slope
    By scope951 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-03-2010, 03:43 PM

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