Let, Lambda, and py_Eval

Posted on April 21, 2024 by dougaj4

I have covered the new Excel Let and Lambda functions before at:

Two New Functions; Let and Lambda

Eval and Let examples

Lambda and VBA

The Lambda function is now available to all Microsoft 365 subscribers, so I have now looked at some examples comparing Let and Lambda with my Python based user defined function, py_Eval.

The spreadsheet can be downloaded from: Let_Lambda.zip and the required Python code from: py_SciPy.zip. The Python function also requires pyxll to connect to Excel. See python-and-pyxll/ for details.

The screenshots below show examples of the three functions used to evaluate the same formulas.

py_Eval takes three arguments: a cell reference with the formula to be evaluated and two column ranges with the variable symbols and their associated values.

Let input is alternate variable names and values followed by the formula as the last argument. Not that the formula must be entered directly in the function input:

The first Lambda option is to enter the variable names followed by the formula, then the variable values are entered in a separate set of brackets:

The second Lambda option is to enter the function in the name manager and give it a name:

The function then operates as a User Defined Function (UDF) with the allocated name:

The py_Eval function calls Python code and will therefore accept any available Python function as part (or all) of the formula:

In the next example the initial input is the X and Y components of a beam length, which are used to calculate the angle of the beam to the X axis using py_Eval. Note that the function must use the Python arctan2 function (rather than ATan2) and the order of the input is dy, dx, rather than dx, dy:

The angle theta is then used by py_Eval with the axial and shear forces to evaluate the formulas in cells A17 and A18:

Using Let, the input arguments can define a variable used by a later argument, so in this case the values dx and dy are input and used to calculate theta (using ATan2), which is then used in the final formula:

Lambda does not allow the calculation of arguments within the function definition, so the theta value calculated on the spreadsheet must be used as input, as for py_Eval:

In the next example py_Eval is first used to evaluate a formula with 5 variables in the usual way:

py_Eval can also return a formula with the variables replaced by their assigned values,, by setting the optional “out” argumant to zero. This formula is evaluated by py_Eval, omitting the variable and value arguments:

The formula in cell A 34 may also be evaluated by Let or Lambda, but they both require at least one argument to be defined, even though it isn’t used:

Finally an example of a very long formula (from a discussion at Eng-Tips), shown with the original formula evaluated, the formula variables converted to values, then this formula evaluated. The original formula used the Excel LN function (natural logarithm), which has been changed to the Python equivalent, log:

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , | Leave a comment

SLS Concrete design with Python

Posted on April 13, 2024 by dougaj4

I am in the process of converting the SLS design related functions in the RC Design Functions spreadsheet to Python code. The current version can be downloaded from:

py_RC Elastic.zip

For details of the pyxll app required to conect the Python code to Excel see Python and pyxll

The functions currently available are:

py_EStress, py_EStressCap, py_PLCurve, py_TStiff:

MomCurveTSN:

py_CircE:

Posted in Concrete, Excel, Link to Python, Newton, PyXLL, UDFs | Tagged , , , , , , , | Leave a comment

RC Design Functions 9.06; Momcurve functions update

Posted on March 30, 2024 by dougaj4

The RC Design Functions spreadsheet has now been updated to Version 9.06, and is available for free download from:

RC Design Functions9.zip

The new version fixes a problem with the MomcurveTS function, which was returning an error for any input strain greater than 0.0005. An example of the updated function is shown in the screenshot below:

For more details of how the function works, and how to use it, see: https://newtonexcelbach.com/2015/10/13/solving-non-linear-equations-with-two-or-more-unknowns-4/

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , | Leave a comment

Download Problems

Posted on March 19, 2024 by dougaj4

Update 24th March 2024:

The certificate problem has now been fixed, and most download links should now work without a problem. Note that any file with VBA code (which is almost all of them) will have the VBA blocked with no obvious unblock option. The procedure to unblock the code is actually quite straightforward and is detailed at: https://newtonexcelbach.com/2023/04/28/two-views-on-vba/.

If there are any remaining problems, please let me know with a comment.

Original post:

It seems there is a problem with the certificate for my downloads folder, which generates security warnings if anyone tries to download anything. I am working on fixing that, but in the meantime the link below should allow download of any of the files posted here:

Download files

Please let me know if any problems downloading, or if anything is missing.

Posted in Computing - general, Excel, Link to dll, Link to Python, Python Pandas, PyXLL, UDFs, VBA | Tagged , | 1 Comment

Solving the 4 point lift problem …

Posted on March 10, 2024 by dougaj4

… with Excel, Python and pyxll.

The problem involves the lifting of a precast concrete panel (or any other long thin object) with lifting points at four sections along the length of the panel, and with the load connected to the crane hook through 3 slings with 3 pulleys:

The spreadsheet 4PointLift.xlsb has been added to the py_SciPy download, along with two new Python functions added to the pyScipy3.py file. The updated files can be downloaded from:

py_SciPy.zip

The spreadsheet uses the pyxll add-in to call Python code from Excel. See pyxll for more details and download.

Assumptions in the analysis are:

  • The pulleys have negligible friction.
  • The crane lifting force at the top pulley is vertical.
  • The panel rotates about the left end of the panel, which does not move horizontally.
  • Bending deflections of the panel and axial deflections of the panel and slings are negligible.

With these assumptions it can be shown that:

  • The legs of the top sling must have equal and opposite angles to vertical.
  • Each leg of the top sling bisects the angle at the top of the attached lower slings.
  • The total length of each sling is unchanged.

The required input data is:

  • The length along the panel to each lifting point, measured from the left; L1 to L4 in the diagram above. L5 is only required for plotting the lift diagram.
  • The three sling lengths.
  • The angle of the panel to horizontal.
  • Guessed coordinates for the 3 pulley positions, with origin at the left end of the panel.

With this data the XY coordinates of the 4 lifting points can be calculated:

The problem can be solved using the general purpose solver function py_SolveFS, together with the LiftCoords function. To use py_SolveFS the input must be rearranged to single column ranges, as shown above.

The function LiftCoords evaluates the six equations shown below.

py_SolveFS combines the Targets and Values arrays into a single list of lists which it passes to the Scipy root function, that passes the data onto LiftCoords, together with adjusted “Guess” data, until all the returned values are sufficiently close to zero.

def LiftCoords(x0, values):
    Target = values[0]
    knownvals = values[1]

    a, b, c, d, e, f, g, h = knownvals
    o, p, q = Target
    
    i, j, k, l, m, n = x0
    

    res = np.zeros(6)

    res[0] = ((((i-a)**2)+((j-b)**2))**0.5)+((((c-i)**2)+((j-d)**2))**0.5)-o
    res[1] = ((((k-e)**2)+((l-f)**2))**0.5)+((((g-k)**2)+((l-h)**2))**0.5)-p
    res[2] = ((((m-i)**2)+((n-j)**2))**0.5)+((((k-m)**2)+((n-l)**2))**0.5)-q
    res[3] = (m-i)/(n-j)-(k-m)/(n-l)
    res[4] = (2*(np.arctan2((n-j),(m-i)))-np.arctan2((j-b),(i-a))-np.arctan2((j-d),(i-c)))
    res[5] = (2*(np.arctan2((n-l),(m-k)))-np.arctan2((l-f),(k-e))-np.arctan2((l-h),(k-g)))

    return res

After root finds the solution to the 6 equations py_SolveFS returns the final 6 values (the XY coordinates of the 3 pulley locations) to the spreadsheet, together with estimated errors and solution time.

As an alternative to using py_SolveFS, I have written a problem specific function to allow the input and results to be in a more convenient format. Input and output for Solve4PointLift are shown above.

The results from the spreadsheet analysis shown above were checked using the Strand7 FEA program. After increasing the stiffness of the concrete and slings by a factor of 100, the Strand7 results were found to be within 0.25 mm of the spreadsheet solver results:

Strand7 plot:

Posted in Concrete, Coordinate Geometry, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, NumPy and SciPy, PyXLL, Strand7, UDFs | Tagged , , , , , , , | Leave a comment