Rotation and translation of coordinates

Rotation and translation of coordinates

WARNING!! WARNING!! THIS QUESTION ALSO POSTED ON MREXCEL.COM

I would like to ask “SHG” a question as it relates to an old post of his, namely:

3D Coordinate Translation and Rotation Formulas for Excel. (LINK NOT ALLOWED!)

excelforum.com/excel-for...for-excel.html

In this you tantalisingly refer to a solution of a problem which may be the same as my problem, you state "I did do a solution, BTW, but it required VBA." I would definitely like to see this solution if still available.

This was a very nice solution to the posted problem, however I also saw this as a possible solution to a problem I had come across myself.

The problem concerns the construction of an in-situ concrete bridge deck. The deck is shaped as a segment of a circle with radius to the setting out line\Master String of 350m. The bridge deck is to cross a motorway and cannot be built at the required location as the motorway has to remain open during construction. The plan is to build the deck on temporary columns at a location close to the final location and transport the completed deck to its final destination during a weekend possession.

I have the following information: Coordinates are on the Master string with radius 350m. Coordinates of the centre points are given in the table below as Xcp,Ycp


Final Location X Y Z Xcp Ycp Radius
Abutment1 110285.205 478353.155 3.745 109952.570 478462.034 350.000
Column 110299.604 478416.560 4.047
Abutment 2 110302.551 478465.733 3.841
Temporary Location
Abutment1 110246.983 478454.145 3.745 109900.000 478500.000 350.000
Column 110249.478 478519.116 4.047
Abutment 2 110243.332 478567.992 3.841

I thought that having 3 coordinates for permanent and temporary situation would mean that as the structure is rotated and translated to its temporary situation and eventually back again that the excel sheet would enable me to calculate coordinates in the temporary situation using coordinates from the permanent situation. However on using the solver to get the Rms error, this gives 4.980 instead of zero? I am probably missing something in the VBA, I am unfortunately not very expert in it. I am hoping that you can comment on whatever it is that I am doing wrong.