In 3D structural analysis (as well as many other applications) it is necessary to convert section properties, forces, and deflections between coordinate systems defined by individual structural members (local coordinates) and the common coordinate system defining the entire structure (global coordinates). For a 2D analysis this is straightforward, but for 3D analysis the transformation needs to be carefully defined to avoid unexpected results. Functions to perform this operation are included in the 3D frame analysis presented here previously (most recently here), but since these functions have other applications I have extracted them and added them to the IP2 spreadsheet.
Download IP2.zip with full open source code.
The new functions are glob_to_loc() and loc_to_glob, which also come in Python versions (py_glob_to_loc() and py_loc_to_glob). Use of the Python versions requires the PyXll addin. See Installing Python, Scipy and Pyxll for more details. Details of usage are shown in the screenshot below (also included in the download file).:
The function arguments are:

Globala or Locala: the values to be converted to the new axis system. The values may be deflections, rotations, forces, bending moments, or section properties (translational and rotational stiffness values). The Globala and Locala arrays may contain 3 or 6 elements; for instance deflections in the X, Y and Z directions, followed by rotations associated with those deflections. Global values are always listed in the order X, Y, Z. See below for the definition and order of the local axes.
 Coord: a 3 element array defining the direction of the longitudinal local axis, relative to the global axis system. In the context of a frame analysis it is the length of the beam element in the X, Y and Z directions.
 Gamma: an angle (in degrees) defining the orientation of the local axes, relative to the default alignment (see below).
 Vertax: 2 or 3, the default vertical direction of the local axes. This value defines both the vertical direction and the ordering of the local axes (see below).
There are a number of options for defining the default orientation and numbering of the local axes; the two I have chosen are:
 Vertax = 2: Local Axis 1 is aligned with the longitudinal axis of the beam, from node 1 to node 2. Axis 2 is perpendicular to Axis 1 and parallel to the XZ plane, so the Y axis is in effect treated as vertical. Axis 3 completes the orthogonal system, being perpendicular to both Axis 1 and Axis 2. This is the system used in the book “Programming the Finite Element Method”, which is the source of many of the routines used in the frame analysis spreadsheets.
 Vertax = 3: Local Axis 3 is aligned with the longitudinal axis of the beam, from node 1 to node 2. Axis 2 is perpendicular to Axis 3 and parallel to the XY plane, so the Z axis is treated as vertical. Axis 1 completes the orthogonal system, being perpendicular to both Axis 3 and Axis 2. This is the system used in the program Strand7, which I have used to check the results of my spreadsheets.
The screenshot below shows the results of generating four Ibeams in Strand7, using the default local axis system:
Note that Axis 2 for all four beams is parallel to the XY Plane. Also note that by default the web of the I beams is also aligned with Axis 2.
The next screenshot shows the result of rotating the view of the model, so that the Y axis is vertical, rather than the Z axis:
Now the webs of the horizontal members are vertical, but note that the web of the inclined member is now inclined.
The simplest way to achieve a vertical web orientation in all members is to use the Z axis as the vertical direction, and rotate the principal axes of all the beams through 90 degrees, as shown below:
The two screenshots below show the results of the glob_to_loc function for beam aligned with the X axis, and with Vertax set to 2 and 3 respectively. The results are the same except that Local Axes 1 and 3 are swapped and the sign of the Axis 3/1 values has changed. Note that applying loc_to_glob with the appropriate Vertax value returns the original data.
Similar results are seen for a beam lying in the XY plane:
The sign of the Axis 3/1 values can be changed by applying a rotation of 90 degrees:
For an inclined beam the changing the Vertax value leaves the axial results unchanged (Axis 1/3), but the other axes have differing results, due to the differing rotations of the beams. The function results are compared with results from Strand7, showing exact agreement:
The results of the different Vertax options may be equalised by applying a rotation to one of the beams, bringing the beam principal axes back into alignment:
Not sure this will help after the cows have left the barn, but it took me a while to find out Elie Cartan figured out a simple way of handling change of coordinates. It is based on earlier work of Hermann Grassmann.
All you need to know is dx dy = dy dx. The first consequence is dx dx = 0.
For example, to convert from rectangular to polar you know x = r cos theta and y = r sin theta,
so dx = r (sin theta) dtheta + dr cos theta and dy = r cos theta dtheta + dr sin theta.
(First times the derivative of the second + derivative of the first times the second. Be careful not to commute the differentials without using the rule dx dy = dy dx.)
Hence dx dy = r (sin theta) dtheta dr sin theta + dr cos theta r cos theta dtheta. I learned the distributive law of multiplication is called the FOIL method from my friend that teaches high school math: First Outer Inner Last. Since dx dx = 0, this turns into the OI method since the first and last are zero.
Scalars still commute even though differentials don’t so
dx dy = r(sin theta)(sin theta) dtheta dr + cos theta r cos theta dr dtheta
= (r sin^2 theta + r cos^2 theta) dr dtheta
= r dr dtheta.
The beauty part of this is that it works in any number of dimensions for any coordinate change.
LikeLike
Thanks for the suggestions, I’ll try and find some time to follow it up. I suspect that the rotation matrix I did use works out to the same thing as the Cartan method, but I’m not sure.
I should probably find a good book on this. The Web has a huge amount of information, but much of it is contradictory, and most of it seems to be written for people who already know it all anyway. I spent too much time looking into using Quaternions for rotations, but in the end I found it was just too hard to get consistent results, and for my purposes it worked out slower anyway.
LikeLike
R. C. Buck, Advanced Calculus, has an appendix describing this. Next time you need to do this give me a shout and I’ll walk you through it. The only thing difficult about it is that it to too easy. 🙂
LikeLike
Keith is describing the exterior product of two vectors (this can be thought of as similar to the cross product but considered as a directed area perpendicular to the normal vector). It provides an alternative way of thinking of the standard Jacobian transformation for changes in coordinate systems. Cartan’s theory of differential forms is very beautiful and extends well beyond this allowing for coordinate free descriptions of physical theories and generalising the fundamental theorem of calculus to many dimensions.
For concrete applications in 3 dimensions i agree with Doug that matrix rotations are a simple way to go about things as they can easily be composed. In two dimensions complex coordinates can be useful too. The quarternion approach can be thought of as an extension which has a few advantages over the matrix approach from what i gather but the order dependence makes it less simple conceptually – for me anyway.
(disclaimer: this is just my viewpoint partly based on a course in Lagrangian mechanics a while ago so may not be totally accurate.)
LikeLike
Hi Doug,
How would I modify this spreadsheet to transform global nodal forces to a local coordinate system aligned with a plate edge normal? I think the “member vector” would be a vector defining the plate edge, and the “rotation about long axis” would possibly be the “angle” of the normal, if that makes sense? Any ideas?
LikeLike
Daniel – In the case of a plate the local axis system is defined by the plane of the plate, so you will have a longitudinal and transverse axis in that plane (for a rectangular plate the axes will be parallel to the sides of the plate) , and a third axis perpendicular to the plane of the plate. If the plate is on or parallel to the XZ plane or XY plane you can use Vertax = 2 or 3 respectively, and Gamma = 0. If the plate is not parallel to one of those planes you need to calculate the angle of the plate transverse axis to the “horizontal plane” (XZ or XY), and that is the gamma angle.
Does that give you enough to work on?
LikeLike
Pingback: Global to Local for plates (and three node beams)  Newton Excel Bach, not (just) an Excel Blog
I’m working with a software called SPACE GASS which uses Vertax: 2 rule. For each member it has either a directional node to specify the rotational angle of the local y coordinate or use just degrees to specify how much the local y is rotated from the global Y. Can you please advise about an easy way of using the above Localtoglobal function (or vice versa) knowing only the member node coordinates and the direction node coordinate?
Thank you a lot
Nour
LikeLike
Nour – see https://newtonexcelbach.wordpress.com/2014/03/02/globaltolocalforplatesandthreenodebeams/
It’s always a good idea to look through the comments for any “Pingbacks” from this site, as that’s the easiest way to find updates on any topic.
LikeLike
Hello again Doug,
I downloaded the glob_to_loc3 function thank you for that. However, for some reason I do not get the answer I’m expecting for members that are perfectly in Z axis.
example: load(0,5,0,0,0,0), node1=(0,5,0), node2(0,5,5), node3(0,7,3) AxiType=2 (that mean the local y axis will be vertical based on the node3 coordinate)
the expected results in this case would be (in xyz sequence) 0,5,0,0,0,0 however I got 0,0,5,0,0,0 (I used the Excel sheet provided and only changed the numbers of the first example in the Glob_Loc3 tab) i.e the forces are in the local z coordinates.
Can you please help to find where is my mistake?
Kind regards
LikeLike
Hi Nour,
It looks like there is a problem with the glob_to_loc3 function with AxType 2 and the local x axis in the Global Z direction. I will check the correction and post here as soon as I am happy with it. If you would like an advance copy to check please send an email to dougaj4 at gmail.
LikeLike
Pingback: How To Convert Rad To Degrees In Excel  Ho Good
Hi Doug,
I use two kinds of sensors:
3axes accelerometer sensor (measuring linear acceleration)
3axes gyroscope sensor (measuring angular velocity)
Both sensors are fixed in a box and placed on the ground (in the study area).
The output quantities measured in the body frame (coordinates of the box) rather than the global frame.
To keep track of orientation the signals from the rate gyroscope are ‘integrated ‘.
To track of position, the three accelerometer signals are converted into global coordinates using the known orientation, as determined by the integration of the gyro signals.
The global acceleration signals are then double integrated to calculate the position.
I think your excel sheet (IP2.xls) will be helpful but when I opened it and press the button enable editing a message appear (The code should be converted to 64bit) and everything stops working.
Maybe because we use different versions. Can you help me, please?
Regards
LikeLiked by 1 person
The error message is:
Microsoft Visual Basic for Applications
compiler error
The code in this project must be updated for use on 64bit systems. Please review and
declare statements and then mark them with the PtrSafe attribute.
LikeLike
I know there are issues with 64 bit Excel with some of my programs, but it is difficult for me to fix them because I only have the 32 bit version.
In this case there is a VBA timer module that calls an external dll, that might well be causing the problem. The timer is not required for any of the functions, so deleting the module might fix the problem:
Press AltF11 to open the VBA Editor.
Click the + next to class modules, then right click ClsTimer and select Remove ClsTimer, then No (for export before deleting)
Please let me know if that works, or email (dougaj4 at gmail) if you would like a copy with the timer module removed.
LikeLike
Also see today’s post (30 May 2017) with a new version of IP2, which has the timer module removed.
https://newtonexcelbach.wordpress.com/2017/05/30/updatetoglob_to_loc3andloc_to_glob3functions/
LikeLike
Dear dougaj4,
Thank you very much for your fast help.
Best Regards
LikeLike
Pingback: Update to Glob_to_loc3 and Loc_to_glob3 functions  Newton Excel Bach, not (just) an Excel Blog
hello, i’d like to transform points on excel in two different coordinate systems. Both coordinate systems are local and i have know some points in both coordinates system
thanks
LikeLike
Do you want to transform the coordinates between the two coordinate systems?
If you know the angle between the axes in the two different systems you could use the Rotate function on the RtoP sheet.
LikeLike
Thank you, but the problem is to determine the need it traslation, rotation and scale between two pairs of points (same points but in different coordinate system) i did thanks to the SVD of the covariance matrix
LikeLike