Following a comment here I have updated the Glob_to_loc3 and Loc_to_glob3 functions to deal with some problems when the local longitudinal axis is parallel to the global Y or Z axes. The IP2 spreadsheet, including the revised functions, can be downloaded from:
IP2.zip
The screen shots below show documentation for the functions, and an example of results with a beam parallel to the Z axis:
The documentation has been edited to clarify the difference between the axis type options:
The same example is used for both functions, with a beam parallel to the global Z axis, with the direction from Node 1 to Node 2 in the negative Z direction:
:
The function also has an option to return the rotation matrix, R0 (Out = 1):
The download file has the input for the Glob_to_loc function linked to the Glob_to_loc3 input, and the associated Gamma angle, to show that the results are consistent. The input for these (or any other example functions) may of course be freely modified.
Newton Excel Bach, not (just) an Excel Blog 
Thank you for all the good work presented on this site. I have been using the IP2 code successfully, but I am looking into expanding it from 3 data points to fit a cluster of 3D points. I found the below linked site providing exactly that, but programmed in Python. Would there be an interest in converting this code into VBA (Way above my programming skills)?
https://meshlogic.github.io/posts/jupyter/curve-fitting/fitting-a-circle-to-cluster-of-3d-points/
Note to IP2: If the 3 data points are on a straight line then AC^2 – AD^2=0 and the program returns an error message for the code line: CD = (AC^2 – AD^2)^0.5
LikeLike
Hi Soren, thanks for the link, looks good. I doubt if I will have time to convert to VBA, but I will look at running it from Excel via xlwings.
LikeLike
That would be awesome! I am looking forward to trying it out 🙂
LikeLike
See https://newtonexcelbach.com/2018/02/11/fitting-3d-circles-to-scattered-points/
Any problems or questions, please let me know.
LikeLike
Pingback: Fitting 3D circles to scattered points | Newton Excel Bach, not (just) an Excel Blog
Thanks dougaj4, this works great! I installed the Anaconda python package as recommended and the spreadsheet worked flawlessly and it ran fast. I did a few additional verification runs against IP2 – ArcCenP3 and the results came out spot on 🙂 Thanks again 🙂
LikeLike