“This country is my home. This beautiful country, unique in all the world,” Kelly wrote in his statement on why he’s voting Yes earlier this week. “Our First Australians looked after it and shaped it for over 60,000 years and, in doing so, developed a rich and complex culture that is a gift to us all.”
“Recognition is not achieved with fine-sounding words and feel-good statements but by promising to listen,” Kelly added. “There is a huge and stubborn gap in health, education and opportunities between Indigenous and non-Indigenous Australians.” He called the gap “unfair” and said it “diminishes us all”.
It’s a splinter in the mind, a whisper in the heart A feeling something’s missing, some crucial little part It’s business that’s unfinished, a reckoning that’s due If not now, then when? If not us, then who?
It’s a simple proposition to join the new and old A chance to make our country larger in its soul It’s an invitation offered to set our course anew If not now, then when? If not us, then who?
[Chorus] How long can we keep walking with this stone in our shoe? If not now, then when? If not us, then who? We may never get another chance like this again If not us, thеn who? If not now, then when?
Too many falling far behind, shut out of thе deal If you called and no-one heard you, imagine how you’d feel This land was never given, it was taken and then sold But its ancient songs and stories are a gift greater than gold
[Chorus] Oh, the status quo is busted, let’s stop kicking that old can If not us, then who? If not now, then when? It’s business that’s unfinished, high time to see it through If not now, then when? If not us, then who?
This post presents two methods for extracting all the different words from a list and counting the number of occurrences of each word. The spreadsheet, including full open-source VBA code, can be downloaded from:
As an example, I have generated a list of dates starting with the current date, then each successive date between 1 and 28 days later. I have then extracted the month from each date, preceded by the month number, so the results can be sorted from January to December:
The first method uses a User Defined Function (UDF) with a scripting dictionary to extract all the unique words, then count the number of occurrences of each. It also has an option to sort the results, either in alphabetical order, or by number of occurencies:
The main code for the UDF is shown below:
Function CountWords(WordList As Variant, Optional Sortby As Long = 0, Optional Order As Long = 1) As Variant
Dim WordCount As Scripting.Dictionary
Dim NumRows As Long, ItemVal As Long, sWord As Variant, RtnA() As Variant
Dim i As Long, NumRes As Long, key As Variant
' Convert WordLIst to variant array
WordList = WordList.Value2
NumRows = UBound(WordList)
' Set up WordCount dictionary
Set WordCount = New Scripting.Dictionary
For i = 1 To NumRows
sWord = WordList(i, 1)
If sWord <> Empty Then
If WordCount.Exists(key:=sWord) = False Then
WordCount.Add sWord, 1
Else
ItemVal = WordCount.Item(sWord) + 1
WordCount.Remove sWord
WordCount.Add sWord, ItemVal
End If
End If
Next i
NumRes = WordCount.Count
ReDim RtnA(1 To NumRes, 1 To 2)
i = 1
For Each key In WordCount.Keys:
RtnA(i, 1) = key
RtnA(i, 2) = WordCount.Item(key)
i = i + 1
Next
If Sortby > 0 Then RtnA = SortV(RtnA, Sortby, Order)
Set WordCount = Nothing
CountWords = RtnA
End Function
Code for the SortV function is included in the download file.
Note that a reference to the scripting dictionary library must be enabled. In the VBA editor, select Tools-References, scroll down to Microsoft Scripting Runtime, and select.
The second option is to use the built-in UNIQUE function, available in recent releases of Excel, and then use COUNTIF to count the number of occurrences of each word.
For more examples using the VBA scripting dictionary, and links to detailed help, see:
The Python 3DFrame spreadsheet has been updated to allow for analysis including torsional warping effects. The new spreadsheet and associated Python files can be downloaded from:
The download file includes four sample spreadsheets comparing analysis results with output from Strand7 and Mastan2. The first analysis was the small concrete frame described in the Mastan2 post. This has been updated using steel I sections, as shown below.
The steel section properties were taken from the Mastan2 library, with units set to mm and Newtons, using the sections M318x18.5, M200x9.2, M250x11.2, and M250x13.4. The section properties generated in Mastan2 were copied to the spreadsheet as shown below:
The analysis in Strand7 (torsional warping not included) used m and kN units, with the results shown below:
Node deflections
Reactions
For the analysis including torsional warping the following changes were made to the model, to suit the default input options in Mastan2:
The coordinates were modified so that the Y axis was the vertical direction, as described in the previous post.
The I beam section properties were generated with the web parallel to the vertical (Y) axis.
Applied loads were modified to constant loads over the full member length, with just one point moment load applied at a node.
All section properties and loads were converted to mm and Newton units.
The spreadsheet and Mastan2 results with torsional warping effects included are shown below, with good agreement between the two results.
Changing the spreadsheet analysis to “Exclude Warping” differences from the Mastan2 reaction results are shown below:
In this case there are significant differences in the Mz/My moments (i.e the torsion in the frame vertical members), but differences in other actions are small.
Microsoft recently announced that it is now possible to access Python from within Excel worksheets. This functionality is currently only available to Excel 365 users on the Insiders – Beta channel, who can access it at no cost. When it is released to all it will be an additional subscription service.
It was announced with a good deal of hype by Microsoft, but here are two (very different) reviews from other sources.
This is a new product for Microsoft, but our initial impression is that it is disappointing. It is our belief that the design decisions taken will, if used, result in less maintainable, error prone, and slower spreadsheets.
Microsoft have added a new “PY” function that will execute arbitrary Python code, remotely in an Azure container running their own Anaconda distribution. We all know the problems associated with VBA code being coupled with workbooks, and now with this PY function the situation is worse as a spreadsheet can contain any Python code in any cell, so code will end up copied and pasted everywhere in a way that will be impossible to maintain. You can’t structure your code into packages, all of your code is written in the cells in the workbook.
This is virtually the complete opposite approach taken by PyXLL, which is to get code out of Excel to a place where it can be shared and re-used across workbooks. This gives Excel users a clean, manageable toolkit of Excel functions, written in Python. PyXLL functions run in your own Python environment and so you can import and use your own packages.
… but see the link for full details and a detailed comparison of pyxll and Python-in-Excel features.
For a different viewpoint, see the YouTube video below from Leila Gharani, which gives an enthusiastic summary of the capabilities of the new feature, together with a demonstration of some of the basic ways it can be used.
Mastan2 is a free frame analysis program based on Matlab. The linked site provides alternative downloads for those with or without Matlab installed. The site also provides a link to the book Matrix Structural Analysis, 2nd Edition, by McGuire, Gallagher, and Ziemian, which provided a theoretical and numerical basis for the analysis routines in the program. The book is available as a pdf at no cost, or US$30 for a print version.
My main reason for interest in this program is that it provides analysis of torsional warping effects in frame structures, which I am adding to my 3DFrame spreadsheet. To check the results of my spreadsheet I have analysed the small 3D frame used in previous exercises, and compared the results with and without restraint of torsional warping effects.
When comparing different 3D frame analysis programs it is important to ensure that they use the same conventions for the orientation of beam local axes, or if not to adjust the frame set-up to provide equivalent models. In 3DFrame the alignment of the beam local axes is based on the same principles as used in the Strand7 FEA program, as described in more detail here:
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 X-Y plane, so the Z axis is treated as vertical. Axis 1 completes the orthogonal system, being perpendicular to both Axis 3 and Axis 2
The Strand7 version of the frame model examined in this post is shown below with local axes displayed:
In Mastan2 the principles used are not clearly stated (as far as I could see), but the program assumes that gravity loads are in the Y direction, so Y was treated as the vertical axis. The local axes are then defined as:
The local x axis is along the longitudinal axis of the member.
The local z axis is perpendicular to the x axis, and parallel to the global XZ plane.
The local y axis forms the third axis of the orthogonal system.
The frame coordinates were modified for Mastan2 so that the Y axis was vertical, as shown below:
The Mastan2 x, y and z local axes are equivalent to Strand7 local axes 3, 1, 2. When entering section properties the Strand7 values I1 and I2 are equivalent to Iyy and Izz in Mastan2, but note that the values are entered in reverse order (Izz then Iyy) on the Mastan2 input screen:
Input of the 3DFrame member properties follows the same conventions as Strand7:
In Mastan2 the materials properties are entered separately, and the density value is entered in force units, rather than mass.
In Mastan2 loads must be applied as node loads or distributed loads along the full length of the member. For this exercise distributed loads were applied in the global axes directions, plus gravity loads. The loads applied in Mastan2 must be adjusted as follows:
Vertical loads must be applied in the Y direction, rather than Z.
The Mastan2 Z axis is equivalent to the Strand7 Y axis, but in the opposite sign, so all loads on this axis must have their sign reversed.
The X axes in Mastan2 and Strand7 are equivalent.
The frame analyses were initially run with warping disabled. The 3DFrame Spreadsheet results are shown below, with Mastan2 results pasted below, and the % difference between the results after adjustment:
Y and Z axis results are swapped.
The Mastan2 Z axis results have the opposite sign to the spreadsheet Y axis.
Spreadsheet rotations are in degrees, but Mastan2 rotations are in radians.
Enabling warping analysis in the spreadsheet resulted in large changes in deflections. Note that the warping stiffness used was much higher than the real value for rectangular concrete sections, to magnify warping effects.
Importing the Mastan2 results with warping enabled shows an excellent match for all nodes and directions:
The updated 3DFrame spreadsheet will be available for download shortly, after completion of further checking.
Newton Excel Bach, not (just) an Excel Blog 