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: