Eval and Let examples

Posted on 21/02/2021 by dougaj4

Final example updated 28th Feb 2021, following comment from Craig:

There are many examples of the use of the new Let function on the web (see my previous post on this topic for links). This post compares use of Let with my Eval user defined function (UDF). More details on the Eval UDF can be found at Evaluating text with units and Evaluating Text – Update.

The screenshot below shows the function “FL^3/(3*E*I)” evaluated with the Eval UDF and the Let function:

The Eval function evaluates a function entered as text on the spreadsheet (or entered as a text string within the function), and reads a list of parameters and the corresponding values from the spreadsheet:

Using the Let function, each parameter is entered directly in the function, followed by the value, which may be entered in the function, or refer to a spreadsheet cell (or range). The function to be evaluated must be entered as the last argument of the Let function:

Note that if the final argument of the Let function is a cell reference, the function just returns the text, rather than evaluating it:

A more complex example is shown below. Using the Eval function the parameters are listed in Column A, and the values in Colum I:

The Let function could be used in the same way, but it is also possible to evaluate parameters within the Let function. In the first Let example below (row 41) I used a nested Let function, in this case evaluating the Beta parameter within the function, but that isn’t necessary. Any parameter can be evaluated within the Let function, using the previously defined parameters. This is shown in Row 42, where Beta is defined with … Beta, (K*G/(Cw*E))^0.5. In the third Let example below (Row 43) all of the parameters are defined within the Let function, giving a complete (but not particularly readable) function, requiring no external evaluation of the intermediate parameters on the spreadsheet:

Click image for full-size view

Posted in Excel, Maths, UDFs, VBA | Tagged , , , , , | 9 Comments

Ebony Buckle, New Scientist, and the 52 Hz Whale

Posted on 19/02/2021 by dougaj4

We last heard from Ebony Buckle here nearly 8 years ago, but I noticed her name in an article on the back page of last week’s New Scientist, which noted that her latest work was inspired by the World’s loneliest whale, so here is the song:

… and read more about the whale at The 52 Hertz Whale.

Posted in Bach, Newton | Tagged , , , , | Leave a comment

Two New Functions; Let and Lambda

Posted on 14/02/2021 by dougaj4

Two new Excel functions have recently attracted a lot of attention; these are:

  • LET (now available to Office 365 subscribers and
  • LAMBDA (currently available to Office Insiders program only)

Microsoft documentation can be found at: Announcing LET

Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!

Introducing LET

LET allows you to associate a calculation or value in your formula with a name. It’s names except on a formula level.

LAMBDA: The ultimate Excel worksheet function

In December 2020, we announced LAMBDA, which allows users to define new functions written in Excel’s own formula language, directly addressing our second challenge. These newly defined functions can call other LAMBDA-defined functions, to arbitrary depth, even recursively. With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation in the Excel formula language. LAMBDA is available to members of the Insiders: Beta program. The initial release has some implementation restrictions that we expect to lift in the future. We discussed LAMBDA and some of our research on spreadsheets in a sponsored video presented at POPL 2021.

Further documentation and lengthy user discussion on the LAMBDA function

Examples of the LET function from ablebits.com:

Using LET function in Excel with formula examples

Discussion of the advantahes and limitations of the LAMBDA function:

What Makes Excel’s Lambda Functions so Awesome (and what doesn’t)?

I will follow up in later posts with my own comments, including comparison with my VBA Eval function, and linking to similar, and better functionality with Python and pyxll.

Posted in Excel, UDFs | Tagged , , , , | 3 Comments

Python callable arguments from Excel

Posted on 29/01/2021 by dougaj4

Many Scipy functions have “callable” arguments, i.e. functions that can be called, with the returned data used as input by the calling function. Types of callable arguments include:

  • Functions in the active Python module
  • Functions in any active loaded library
  • Lambda functions

When the callable argument is passed from Excel, using pyxll, the function name will be passed as a text string, which must be converted to a function object in the Python code. Different procedures are required for the three types of function listed above:

  • Functions in the active module can be called with the “globals” method (The globals () method returns the dictionary of the current global symbol table. )
  • Functions in active loaded libraries can be called with the “getattr” method (The getattr() method returns the value of the attribute of an object.)
  • Strings in lambda format can be converted to lambda functions with the eval() function

Examples of each of these methods are included in the code below:

import scipy as sp
import scipy.stats as stats
import numpy  as np

mods = {'np': np, 'stats': stats, 'sp': sp}

def GetCallable(func, mod = None):
    # Convert string to callable
    # Remove spaces and = from start of string
    func = func.replace('=', '')
    func = func.strip()
    
    # If string starts with 'lambda' convert ^ to **, then convert string to lambda function
    if func[0:6] == 'lambda':
        func = func.replace('^', '**')
        func = eval(func)
    # Else if module is not specified, convert string to function from globals
    elif mod is None:
        func = globals()[func]
    # or if mod is specified, convert mod string to module, then func string to function from mod 
    else:
        mod = mods[mod]
        func = getattr(mod, func)
    return func

The output from this function is a function object that can be passed to any function requiring callable arguments:

@xl_func
@xl_arg('x', 'numpy_array', ndim = 2)
@xl_arg('y', 'numpy_array', ndim = 2)
@xl_arg('rank', 'numpy_array<var>', ndim = 2)
@xl_arg('weigher', 'str')
@xl_arg('mod', 'str')
@xl_arg('additive', 'bool')
def py_weightedtau(x, y, rank = None, weigher = None, mod  = None, additive = None):
    """
Compute a weighted version of Kendall's :math:`\tau`.
   ...
Function argument descriptions
    ...
    """
    if weigher is not None: weigher = GetCallable(weigher, mod)
    
    kwargs = {'rank': rank, 'weigher': weigher, 'additive': additive}
    kwargs = {k:v for k,v in kwargs.items() if v is not None}

    return stats.weightedtau(x, y, **kwargs)[0]

Posted in Excel, Link to Python, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , | 1 Comment

Python optional arguments from Excel – Part 2

Posted on 19/01/2021 by dougaj4

The previous post provided a method for using pyxll to pass optional arguments from Excel to Python whilst preserving the default values of any called Python function for arguments that were omitted in the Excel function.

One condition where this does not work is for Python functions where “None” is a valid argument, but it is not the default. For instance, many Scipy functions working with multi-dimension array input have an “axis” argument that allows “None” as input, but the default is 0. In this case the default value for the Excel function should be set to 0, and the data type set to variant. Before calling the Python function two additional steps are then required:

  • If the argument is a number, convert it from “float” to “int”.
  • If the argument is the string “None” convert it to the Python None object.

Typical code is shown below:

@xl_func
@xl_arg('a', 'numpy_array', ndim = 2)
@xl_arg('axis', 'var')
@xl_arg('dtype', 'str')
@xl_return('numpy_array')
def py_hmean(a, axis = 0, dtype = None):
    ...
    if axis != 'None': axis = int(axis)
    kwargs = {'axis': axis, 'dtype': dtype}
    kwargs = {k:v for k,v in kwargs.items() if v is not None}
    if axis == 'None': kwargs['axis'] = None
    res = stats.hmean(a, **kwargs)

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