from Sandy Denny:
Not to be confused with:
Recent Comments
Green grow the laurels
Posted in Bach
Tagged Green grow the laurels, Once I had a sweetheart, Pentangle, Sandy Denny
Leave a comment
3DFrame update
The update to the Glob_to_loc3 and Loc_to_glob3 functions (and associated functions) has now been transferred to the 3DFrame spreadsheet, which will now give correct results when beams are defined in the global negative Z direction. The revised spreadsheet (including full open source code and compiled solver files) may be downloaded from:
3DFrame.zip
See 3DFrame with spring releases for more details, including examples, and 3D Frames, axes and stiffness matrices for information on local axis conventions used, and details of the procedure for generating the 3D stiffness matrix.
Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, Strand7, VBA
Tagged 3D frame analysis, Excel, moment release, spring end release, Strand7, VBA
15 Comments
Update to Glob_to_loc3 and Loc_to_glob3 functions
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.
Posted in Coordinate Geometry, Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA
Tagged Excel, global axes, Glob_to_Loc3, local axes, Loc_to_Glob3, UDF, VBA
6 Comments
Working with milliseconds in Excel
Edit 3rd Jan 2026: This download is not currently available.
A recent post at Eng-Tips looked for a solution to problems with working with times in Excel, when time differences are required to the nearest millisecond.
The problem is that although times may be entered as a time/date value, and formatted to display milliseconds, the format bar displays the time to the nearest second, and if you press F2 to edit a value, the time value is actually rounded to the second. Pressing F2 then enter not only changes the displayed value, it changes the actual number:
The simplest way to avoid the problem is to enter the number as text, by starting with ‘ . Pressing F2 then Enter will not change the text, but simple arithmetic will work:
Using text does have a few disadvantages though:
- The time is rounded to the nearest millisecond, so if greater precision is required, this is not a suitable solution.
- It is not possible to include days in the text, although an hours value greater than 24 is interpreted correctly.
- The text may be misinterpreted if it is not entered in full. For instance ‘8:23 is interpreted as 8 hours 23 minutes, but ‘8:23.0 is interpreted as 8 minutes 23 seconds.
An alternative approach is to convert the time to a date value in a formula, which will not be rounded by Excel. A simple user defined function (UDF) that will do the calculation is shown below:
Function msTime(Secs As Double, Optional Mins As Double, _ Optional Hours As Double, Optional days As Double) As Double Const SecsinDay As Long = 86400, MinsinDay As Long = 1440, HoursinDay As Long = 24 msTime = days + Hours / HoursinDay + Mins / MinsinDay + Secs / SecsinDay End FunctionExamples of usage of the UDF are shown in the screenshot below. Note that:
- Differences of less than 1 millisecond are retained
- Days may be included if required
- All the arguments other than seconds are optional
- The values may be entered as cell references, or directly as values, as for a built-in Excel function
A spreadsheet including the examples shown above, and all three lines of VBA code, may be downloaded from msTime.xlsb
Posted in Computing - general, Excel, UDFs, VBA
Tagged Excel, milliseconds, msTime function, time format, time rounding, UDF, VBA
1 Comment
Using Matplotlib from Excel with xlwings – update
In February 2016 I posted a spreadsheet with examples of linking to the Python Matplotlib library with xlwings. Since then updates to xlwings required some changes to the python code, specifically plot.show has been replaced with pictures.add.
The spreadsheet has now been updated, and can be downloaded from:
xlMatPlot.zip
Similar code has also been updated in the xlwScipy spreadsheet:
xlScipy-xlw.zip
A typical graph example is shown in the scree shot below:
See linking to the Python Matplotlib library for more details. The code in the post has now also been updated.
Posted in Charts, Charts, Excel, Link to Python, Newton, NumPy and SciPy, VBA
Tagged Excel, MatPlotlib, Python, update, VBA, xlMatPlot, xlwings, xlwScipy
Leave a comment
Newton Excel Bach, not (just) an Excel Blog 