Calculating Rotation of a Displaced Plane with Respect to its Starting Position

Hi all,

I have a rigid body that is rotated by an unknown amount. All the data I have is four points where the plane started and four points where it ends up (displaced). From this data, my approach has been to find the normal vectors of the planes and then describe the rotation using quaternions. The angle part is derived as the dot product between the two normalised vectors and the vector part as the cross product between them.

I've tried to then use a few various methods in order to rotate my original points back to the displaced points, .e.g. P' = qPq' and creating rotation matrices. Whilst I can manage to get this to work for simple cases, like rotating by 90 degrees around Y or X, I cannot get them to work for cases where the the rotation is about the Z axis (presumably because the two normal vectors are parallel) or cases where there are a number of rotations around a different axes. I've been studying quaternions, rotations and working on this all weekend and I feel like I've just hit a brick wall here. Any insight would be much appreciated.

I've uploaded my messy spreadsheet where I've been playing around with different methods. By the way, the points and 'rotated' points I have generated using a CAD program.

If anyone is wondering about where I've tried using LINEST, it's because eventually my 'displaced' points won't make a perfect plane and I will have to somehow estimate a plane from them.

Cheers


EDIT
The above question is somewhat out of date, please see below:

I've now attached my latest spreadsheet. I've discovered that in fact, I need to rotate by using two quaternions rather than just one. The method I'm trying to follow is detailed on this page: https://stackoverflow.com/questions/...99768#47499768 - however it is in some programming language and although I'm trying my best to interpret it and write similar formulas in excel, I'm still not getting the correct answers. I'm not sure if I'm calculating the quaternions correctly, or if I have to use ACOS in the w part and then convert to degrees (although I've tried this also and didn't get the correct answer).

Attach your workbook here. Many members will not or cannot access file-sharing sites.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.