Why do LET and LAMBDA vary in the way they behave when passed a volatile function as a parameter?

Question

We now have LET and LAMBDA expressions in Google Sheets. My understanding is that LET expressions:

1. correspond to a subset of the use-cases of LAMBDA expressions
2. Have their arguments ordered in a more human-friendly way to aid comprehension

On the face of it, it appears that =let(x,1,x) is equivalent to =lambda(x,x)(1), because both return a value of 1.

But consider what happens if we use a volatile function (e.g. RAND) instead of 1:

• =let(x,rand(),x) returns a value of rand() that updates on every edit of the sheet
• =lambda(x,x)(rand()) returns a value of rand() which is fixed

Can anyone help me understand why this behaviour occurs as it's not obvious to me why there should be any difference between the two functions?

Answer 1

I agree with TheMaster in that it's pretty much just the way it's designed, and could be an intended method to "anchor" volatile functions, but here is another way that you can interpret this if you're willing to do enough mental gymnastics. A clue to this could be the definitions of each of these in the Google documentation:

LET function

This function assigns a name with the value_expression results and returns the result of the formula_expression.

LAMBDA function

You can create and return a custom function with a set of names and a formula_expression that uses them.

The keywords here are that LET returns the result of a formula, and on the other hand LAMBDA returns a custom function. So we could say that LET makes its calculations on the fly like regular formulas, while LAMBDA takes the value of RAND() at the moment of calculation, and then returns a function that has that original value. You could see it like the RADIANS() function, which essentially has π built-in as a constant, except that with LAMBDA you're defining the constant.

An exception to this is when you're passing values as a reference. For example, =LAMBDA(x,x)(A1) where A1 is =RAND() does recalculate the value.

This is probably why only LAMBDA works with the helper functions MAP, REDUCE, BYCOL, BYROW, SCAN and MAKEARRAY. These functions expect a LAMBDA or named function, while LET returns a value, not a custom function, so you cannot use them interchangeably here even though they're similar on the surface.

Answer 2

It's just the way it is currently designed. One could make sense of it by saying that the outer scope() is evaluated only once, regardless of the volatile nature of the function, and passed to the inner scope, but the inner scope is always evaluated again, if it contains a volatile function.

=LAMBDA(x,x)(RAND())
        ^        ^
 Inner scope     |
            Outer scope

It's important to realize no other functions can currently be called twice with two ()(), except LAMBDA.