# 3D Coordinate Translation and Rotation Formulas for Excel.

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

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

Oops -- the last column of the transform matrix should be zero but for the last.  Register To Reply

4. ## 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.  Register To Reply

5. ## 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.  Register To Reply

6. ## 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!  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

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

You're welcome.

I did do a solution, BTW, but it required VBA.  Register To Reply

10. ## 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?  Register To Reply

11. ## 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.

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.  Register To Reply

12. ## 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!  Register To Reply

13. ## 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.  Register To Reply

14. ## 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?  Register To Reply