The pyCBA code has now been updated to allow for supports with specified displacements (release 0.6 or later). My py_xlCBA spreadsheet and associated functions have been updated to call the pyCBA code for displacement analysis, rather than using my add-on functions. This has the advantages that it now handles rotations as well as vertical displacements, specified displacements may now be called from the py_CBACache function, as well as py_CBA, and the overall code is much simpler. The updated spreadsheet and Python code can be downloaded from:
The function input is as before except that the restraint displacement range has an additional column for specified rotations:
The next release of pyCBA (0.7) will allow for trapezoidal distributed loads, so the distributed load Load/m input has two columns to allow for that. For uniform distributed loads the second column may be left blank.
Results are shown below for a 3 span continuous beam with cantilevers at both ends and specified displacements and rotations at some supports:
Analysis in Strand7 give near exact agreement for all output:
The py_CBAcache function returns the pyCBA results as a cache object, from which the desired results may be xtracted with py_CBARes or py_CBAReact, without running the analysis again. The Python graphical output generated by pyCBA may also be transferred to Excel by setting the Plot_res input to TRUE:
The Strand7 data and results files for the example beam are included in the download file, and typical output is shown below:
I have added code to the 3DFrame-py module to allow simplified input for 2D frame analysis. The revised code and example spreadsheets can be downloaded from:
A quick plot of the function 2^x – 10x shows that there are solutions at x just greater than 0 and just less than 6:
The simplest and easiest way to get a close estimate of the exact solutions is to use the Excel Goal Seek function (although this wasn’t mentioned in any of the replies on Quora!). It is found under What-if Analysis on the Data tab:
The required inputs are an initial guess of the ‘x’ value and the function to be solved. In this example, since we are looking for the x value to make the function zero, I have multiplied it by 1 million for greater precision. Goal Seek adjusts the value in cell D6 to make cell E6 very close to zero:
In this case starting with x = 1 returns a result of 0.107755, and x = 5 returns 5.877011.
The most frequent solution given in the Quora discussion was to use the Newton-Raphson method to find the two solutions by iteration. This can be done in Excel using the py_Brent function (which is a refinement of the Newton-Raphson method), that calls the Scipy brentq function, and is included in the Scipy download linked above. There is also a VBA version that can be downloaded from:
The py_Brent function requires an input lower and upper bound for the solution, which must evaluate to values with different signs, and a text function in Python lambda format. Note that exponentiation must be in Python format (**), rather than ^.
The third alternative is to use the Lambert W function, which is defined by W(z) = w, where we^w = z. The function has an infinite number of solutions with complex numbers, but for real results there are just two, indicated as W(0) and W(-1). See Lambert W function for more details.
The Lambert W function can be used to find the desired results as shown below:
Scipy has a lambertw function, that can be called from Excel using the pyScipy py_CallfuncS function:
The required input is the function name (lambertw), the function input value and output index (0 or -1), entered in a column, and the number of input rows. The returned value is divided by LN(2) to find the required results.
The py_CallfuncS function does not allow for input or output of complex numbers. To allow that, I have added the py_LambertW function to the pyScipy module, included in the download at the top of the post.
For the current example the input Z value is specified in a single cell, but complex numbers may be specified in two adjacent cells, or multiple complex numbers in a 2 column range. The function also allows adjustment of the target tolerance value, and the option to return complex numbers. By default results are returned as real for real input or complex for complex input, but the function result may be complex for real input with K other than 0 or -1, in which case RtnComplex should be set to True.
The final results using the LambertW functions are as for the other options:
The examples shown above can be downloaded from: Lambert.zip
As stated in the previous post, this post will cover the calculation of beam actions and displacements when one or more supports have a defined vertical displacement, using the pyCBA beam analysis package. The Python code has been updated since the previous post, so the revised code and associated spreadsheets should be downloaded from:
The procedure used for analysis with specified support deflections in the latest update is:
Find results with no displacement at all fixed supports. This is easily done using the original input data ranges, but select only the first three columns of the Supports data.
At the first support with a specified displacement, set the support translation restraint to 0 and apply unit upward force.
Calculate the force required for the specified displacement: Specified displacement/displacement due to unit force
Apply this force at the support, and add the resulting actions and displacements to the results from the first stage.
Repeat for each support with a specified displacement.
This procedure is shown step by step in the screenshots below and is included on the DefStages sheet of the py_CBA-def-stages.xlsb file. The final results are compared with the results of a Strand7 analysis, with near exact agreement.
Analyse with no deflection at fixed supports:
At the first fixed support, set translation restraint to zero, and apply unit upward force:
Apply force required for the specified displacement, and add results to the first stage results:
Repeat for Support 2:
and Support 3:
Final results are in near exact agreement with Strand7:
Following the previous post the py_xlCBA spreadsheet and associated code have been updated. The updated files, including open-source code, can be downloaded from:
Note that the link in the previous post was downloading the wrong file, and has just been corrected.
The main updates are:
The py_CBA function now returns an array of beam or reaction results, rather than the py_CBA cache object.
The cache_object is now generated using the py_CBAcache function.
Beam or reaction results can now be extracted from the cache object using the pyCBARes or pyCBAReact functions.
The beam results now include beam slopes as well as deflections.
The reaction results include the beam slope and deflection at each support, as well as reaction forces and bending moments.
Supports may now be allocated a specific vertical deflection, as well as the displacement and rotation fixed or spring restraints. Note that if a non-zero displacement is specified any spring stiffness for deflections at that support is ignored, but spring or fixed rotation restraints are still applied.
The new input and output is shown below:
The supports input may now be three columns as before, or four columns if specified support displacements are required. Note that there must be at least one support with zero (or blank) displacement.
The main beam results array is now generated using the py_CBA function.
The results cache object is returned as BeamResults@x (lower left), using the py_CBAcache function.
As before, the CBA generated Python graphs may be returned immediately below the cache object.
Results may be extracted from the cache using the py_CBARes or py_CBAReact functions.
As before, the latest functions have been checked against Strand7 analyses of 15 different span arrangements, each with 6 different support conditions. The py_CBA results were generated in the py_CBA-Check26-2.xlsb spreadsheet, and all results are compared with Strand7 in Check py_CBA-16Jan26.xlsb. The results for the different support conditions are copied to six different sheets, and the results for any span type can be displayed by entering the Span Type number in cell Y2. The results are very close, except that currently where a specified deflection analysis is carried out, and the first support is at X = 0, the shear force at that support is shown as zero, as shown below:
The results have also been compared against the py_Conbeam function on the py_CBA-Check v Conbeam-support def.xlsb spreadsheet, with near exact agreement. Results are shown below for a 3 spam beam with two cantilevers, and 3 supports with specified deflections:
The next post will look at the procedure used to calculate the beam actions and deflections with specified deflections at one or more supports.
Newton Excel Bach, not (just) an Excel Blog 