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