In the previous post I covered some why-and-how points of LAMBDA function development and distribution, and introduced the Advanced Formula Environment (AFE). Today I’ll introduce the LET function, and give some follow-up points on LAMBDA and AFE.
LET(name,expression,[name,expression],body)
The LET function is a formula developer’s companion. Whereas LAMBDA gives end users their own lexicon of familiar names for inputs and outputs, LET does the same for developers in giving names to the processing steps that connect input to output. This is mostly invisible to the end user, but can do wonders for legibility and transparency of formulas.
LET is featured as a tool to support LAMBDA. The expanded use and complexity of names that LAMBDA brings certainly benefit from LET. LAMBDA with LET is very VBA-familiar without being intimidating. This supports the formation of good habits as adoption grows.
But LET can be used in any formula, in-cell or in Name Manager. Its purpose is to break apart formula elements into intelligible parts, then put them back together. Each name/expression pair can be independent or can reference a previously-defined name (or LAMBDA input parameter). After all name/expression pairs, the names come back together in the output formula.
LET always has an even number of commas separating an odd number of arguments – pairs of names/expressions, followed by output. At first look, the formula appears to be just a long series of cryptic terms. To read it, substitute “be” for the comma between each name and its expression.
Taking license to make the point, the first formula can be read “let light be true, let there be light, let was be there, and there was light – is true.” It becomes even easier to read when formatted and indented. This can be done in the formula bar with Alt+Enter for new lines, or in AFE. Here, each LET name pair is indented on a separate line. AFE allows Tab indenting and outdenting.
As I claimed with LAMBDA, the formula does not need to be very complex to benefit from LET names. Especially for users who share files, transparency is important. But again, LET is for people who need or want to know what’s happening behind the scenes, which is a different audience than LAMBDA.
Of course, the more complex the formula, the more helpful and even essential it is to break it down with names. Particularly where a formula segment is repeated, better to repeat a short name than a lengthy expression. Consider the following formula, without LET:
Here is the same formula with LET names, each building a block from either LAMBDA inputs or previous LET names:
LET is highly recommended where shared use and maintenance are in practice. But even for a solo user, if you’ve ever struggled to decipher your own complex formulas, you will quickly find the value of proactive LET naming. With a good naming convention it is widely understandable, reducing the need for documentation. You can even include a comment as a LET name without impacting the result, similar to N():
LET(note,”helpful text”,.... (not used in output) or,
+N(“helpful text”) (adds 0 to a numeric value)
Additional LET examples are at the end of this post.
LAMBDA update
In the previous section I noted how names and comments can improve your own and other users’ experience. This does not replace the need for the comment in Name Manager, which is the only way to feed information to the point of use. Currently, that can only be maintained individually in Name Manager, or by VBA as described in my previous post.
In that earlier post I gave a wish list of features. I need to subtract one and add one. I wished for optional arguments, which are already possible using square bracket notation. They should be the last arguments listed. The formula must withstand the absence of the optional argument, as part of standard testing.
For example, in the function below, prefix can be a range, a string, or omitted. If it's a range, a random cell is picked, and if a string, a random character is picked. If prefix is omitted, ISREF(prefix) and LEN(prefix) are both false, and you get “”.
The point to add to the wish list is array arguments. Think of =CELL(info_type, which opens a picklist of text options, or =WEEKDAY( ,[return_type], which opens a list of numeric values with their meaning. This will make LAMBDA yet more broadly useful, while helping reduce proliferation. In the meantime, the design options are to use a “type” argument and list the choices in the comment, or to copy the LAMBDA for each type of output desired.
I am still settling on my preferred naming convention, leaning towards short namespaces and lower-case names. I’m also looking for ways to combine functions with conditional output based on a reasonable set of input selections as described above.
AFE Update
With more practice in AFE, I have both more positive and more negative impressions. I find the Editor easiest to use and rarely look at the Manager. I like the indentation feature, with vertical guidelines to connect the start and end of segments. I like “change all occurrences”, but don’t like that “all” means all names, not just within the current name. I don’t like that when you type a left parenthesis or bracket, it gives you the right as well. This is probably good when you start writing a name in AFE, but it’s clumsy for editing.
As to comments, AFE fails on two counts. First, as previously noted, it does not display nor transfer comments from Name Manager. Second, although you can write yourself comments in Editor as /* comment */, when you sync with Name Manager and reopen AFE they are wiped out. These have limited value anyway, but it’s still frustrating when things don’t work properly.
As I find promise in namespaces, I like that I can manage them in Editor, and can easily cut and paste a set of names from one to another.
Overall, I’m getting more comfortable using AFE Editor, but I still need to hot-test the function in the sheet, and enter comments in Name Manager.
More LAMBDA/LET examples
Comments