Eval and Let examples

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

This entry was posted in Excel, Maths, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

8 Responses to Eval and Let examples

  1. Craig says:

    Doug, In your 1st example, you are returning the cell with the formula string and that’s all. You might as well have written ‘=A29’ as that’s all you’re actually doing. It’s not demonstrating the use of LET() function at all.

    In the second, there is no need to nest the LET() functions unless I’m missing what you’re trying to achieve. Simply define all the individual variables and then define beta using those variables, and then your final formula. You’re doubling up on defining the individual variables when there is no need.

    Like

    • Craig says:

      Actually I see on first point you were actually just pointing out the fact that it returns the text string only, somehow missed the picture above. Second point stands though regarding you can nest variable definitions without requiring nested LET functions.

      Like

      • dougaj4 says:

        Thanks Craig, I’ll update the post to show nested variables as suggested.
        My point with entering the cell address of a text string as the last argument, rather than entering the actual text, was that it would be useful if this worked, but as you noted, it just returns the content of the cell, rather than evaluating it.

        Like

  2. pgalvan@sigmaingenieros.cl says:

    Hi Doug, it seems for me that your funtion is more friendly-user in some ways. I wonder which es the time eficiency of both funtion. Have you compared then?

    Like

  3. TB says:

    Howdy Doug, I’ve been testing out the LAMBDA functionality, particularly with trying to use and LAMBDA version of EVALUATE in the Name Manager to help me debug my LETs and LAMBDAs, would love to hear what you make of it: https://techcommunity.microsoft.com/t5/excel/legacy-evaluate-in-lambda-build-test-lambdas-from-sheet/td-p/2133924

    Like

Leave a Reply to Craig Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.