+ Reply to Thread
Results 1 to 14 of 14

3D Coordinate Translation and Rotation Formulas for Excel.

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Colorado
    MS-Off Ver
    Excel 2010
    Posts
    9

    3D Coordinate Translation and Rotation Formulas for Excel.

    I have a 3D translation and rotation problem I am trying to solve using Excel 2010.

    I am in construction and we are trying to accurately build a complex shaped steel space frame. First we build portions of the structure ground. We call these “modules”. Then we lift the modules and set them in place in their final location.

    We have very accurate 3D model of the structure where we can get X,Y Z coordinates for up to 30 of 40 points within a module. These XYZ coordinates are for the final location of the module on the construction site.

    When we build the module in an arbitrary location on the ground, the module will be translated an arbitrary amount from the X Y Z location and it will also be rotated an arbitrary amount about the X and Y and Z axis as well.

    We will be able to survey the module on the ground in its arbitrary position. I want to come up with a spreadsheet that will automatically calculate all corresponding module points on the ground based on the first 3 points A’, B’ and C’ surveyed on the ground.

    Here is input data example.

    Known

    Final Location Survey Arbitrary Module Location
    X Y Z X Y Z
    A 52.000 -113.000 45.000 A' 54.000 -108.000 51.000
    B 64.976 -106.778 50.757 B' 66.976 -101.778 56.757
    C 54.992 -100.993 40.218 C' 56.992 -95.993 46.218
    D 58.640 98.420 4.560 D' ? ? ?
    E 6.124 7.580 -8.210 E' ? ? ?
    F 18.372 22.740 -24.630 F' ? ? ?
    G 4.100 4,5 45.200 G' ? ? ?
    H -31.120 25.890 7.220 H' ? ? ?
    I -1.580 8.880 6.250 I' ? ? ?

    Draft of 3D translation and rotation conversion.xlsxPoints A’ B’ and C’ have the same distance between each other and the same spatial relationship as A B and C.
    How do I calculate corresponding X Y & Z coordinates for points D’ through I’?

    Thanks,

    CLM

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

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    I used Solver to compute three Euler angles for rotations and three distances for translation, resulting in a 4 x 4 homogeneous transformation matrix, and then applied the same transformation to the other points.

    A UDF calculates the rotation portion of the matrix.

    I think this is correct, but haven't done it in a long time.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

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

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    Oops -- the last column of the transform matrix should be zero but for the last.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-31-2013
    Location
    Colorado
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    Thank you very much! I did some research before I posted this question and found some theory on matrix calculations, but this is way over my head in the math and excel department. Your spreadsheet works perfectly for my intended purpose. I have checked it a few times utilizing CAD. I see your 2nd post indicating "the last column of the transform matrix should be zero but for the last". It seems the spreadsheet works with that column with all 1's or 3 0's and a 1. Thank you again so much for freely sharing your brilliance. I'd hope somehow that I could return the favor, it not to you, then to someone else.

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

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    You're welcome.

    It seems the spreadsheet works with that column with all 1's or 3 0's and a 1.
    It's doesn't matter for this case, but it makes the homogeneous coordinate W=1, which is essential if additional transformations are to be applied to the result.

  6. #6
    Registered User
    Join Date
    09-30-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2007
    Posts
    2

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    I am trying to get Excel to do something very similar to the OP - so I believe this posted solution may work for me. When I look at the file, however, it appears that the inputs to the transformation are the trans x,y,z and the roll,pitch,yaw angles. What I am looking for is to have Excel calculate the 4x4 matrix (rotation with translation), derived from the A,B,C and A',B',C' points. I don't know my translation and roll,pitch,yaw (and don't really care) - I just want to be able to pass additional points (D,E, etc) through the same transform to get D',E', etc.

    Can this file be modified to suit my needs, or does somebody have a better solution? Thanks!

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

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    The posted solution turned out to be insufficient. Here was the problem.

    Given two congruent triangles in space ({A1,B1,C1} and {A2,B2,C2}), find the homogeneous transform matrix that maps one to the other.

    The solution was to

    1) Form a homogeneous translation matrix that puts A1 at the origin,

    2) Form a quaternion rotation that puts B1 along +z (it can't be a Euler angle rotation, because that could gimbal lock). Convert the quaternion to a homogeneous rotation matrix.

    3) Form a rotation about +z to put C1 in the x-y plane

    4, 5, 6) Repeat steps 1 to 3 for the second triangle

    The matrix derived in steps 1 to 3, times the inverse of the matrix in steps 4 to 6, maps triangle 1 to triangle 2.

    Easy for a mathematician, but a struggle for this engineer.
    Last edited by shg; 10-01-2014 at 11:10 AM.

  8. #8
    Registered User
    Join Date
    09-30-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2007
    Posts
    2

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    Thanks for the reply. I agree, those steps will get it done.

    It would be a struggle for this engineer too, so I've decided to "work smarter, not harder" and have found an alternate solution using some CAD software that I have available. Thanks again.

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

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    You're welcome.

    I did do a solution, BTW, but it required VBA.
    Last edited by shg; 10-02-2014 at 05:00 PM.

  10. #10
    Registered User
    Join Date
    12-01-2014
    Location
    Fishers, Indiana
    MS-Off Ver
    2010
    Posts
    1

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    I want to do something similar but i want to input all of the A,B,C.....and A',B',C' values and have the sheet compute the corrections to the heading pitch and roll values. Is that possible?

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    rswingley,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  12. #12
    Registered User
    Join Date
    03-08-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    1

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    I downloaded the above excel example but it is giving me a #name? error for "Rotate3". How do I define Rotate3? I am using Excel 2013. Thanks!

  13. #13
    Registered User
    Join Date
    12-15-2017
    Location
    Holland
    MS-Off Ver
    2003
    Posts
    21

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    Hello,

    any chance of screenshots of the solver showing the necessary steps and cells used in the solver. I can't completely follow what you have done. I am interested as I recently solved a similar rotation problem (simpler!) and would love to completely understand what you have done here.

  14. #14
    Registered User
    Join Date
    12-15-2017
    Location
    Holland
    MS-Off Ver
    2003
    Posts
    21

    Re: 3D Coordinate Translation and Rotation Formulas for Excel.

    Sorry, I just realized that my question was answered by merely opening the solver. I must try to do better!

    In total you have 17 UDF's in VBA. Function Rotate3 is used in the calculation as are the const. R2D, D2R but I can't see how the others are used. Is this maybe part of a VBA suite that you use in your work, some of which is used here for this specific problem?
    Last edited by twa14; 01-20-2018 at 07:20 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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