Point Cloud Rotation

  • Marko,

    In response to your PM:

    The attachment is the same as that in http://www.ozgrid.com/forum/showthread.php?t=86865 with the addition of a pair of plots that show the points before and after rotation.

    z, xy, and other references used in the formulas are dynamic named ranges; do Insert > Name > Define to see, and see http://www.contextures.com/xlnames01.html to learn more.

    Step 1 computes a homogeneous translation matrix to center the points about the origin.

    Step 2 computes the best-fit plane using LINEST.

    Step 3 puts the plane equation in standard form.

    Step 4 normalizes the coefficients, with the result that coefficients A, B, and C are the unit normal vector to the plane.

    Step 5 uses a cross-product to compute the axis of rotation, and a dot product to compute the angle about that axis, to align the normal vector to the z axis. (The 4-element result is, by definition, a quaternion.)

    Step 6 converts the quaternion to a homogeneous rotation matrix.

    Step 7 is the product of the translation matrix from step 1 and the rotation matrix from step 6 into a single transformation matrix. That is what gets applied to the original points starting in line 31.

    EDIT: Each step builds on the result of the prior step except for Step 1, which could be done any time prior to step 7. It was the first thing that came to mind as I was pondering the problem, so I did it first.

  • Re: Point Cloud Rotation

    Dear shg!
    Thank you very much for your quick and efficient explanation. I am very impressed with so simple and elegant solution in Excel only. As you perhaps can imagine, elegant solutions are sometimes quite hard to catch (at least by me:-)). I will dig into this, especially dynamic range.

    Thanks and best regards,