Forum Discussion
Does a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
The short answer is "yes", the expensiveMatch variable is evaluated whether the value argument of IFNA evaluates to #N/A or not, because LET uses eager evaluation to compute expressions as soon as they are assigned (with the exception of TYPE 128 variables defined as functions, e.g. LAMBDA, which are deferred until they are called).
The easiest way to illustrate this point is to conduct a basic test with an expression that takes a long time to evaluate. For example:
= LET(
x, 1,
y, SUM(EXPAND(1,5000,5000,1)),
IFNA(x, y)
)There will be a noticeable calculation lag when committing the above-mentioned formula to a cell because LET evaluates SUM(EXPAND(1,5000,5000,1)) as soon as y is defined.
Now, compare that to the following:
= LET(
x, 1,
IF(
ISNA(x),
SUM(EXPAND(1,5000,5000,1)),
x
)
)-OR-
= IFNA(1, SUM(EXPAND(1,5000,5000,1)))Both of these examples will return 1 instantaneously.
When IF receives a scalar as its logical_test, it will only evaluate the applicable value_if_true or value_if_false argument. Likewise, IFNA will only evaluate the value_if_na argument if it receives a scalar as its value, which then evaluates to #N/A.
The exception for both functions would be if they receive an array object (even a single element array), which would trigger all arguments to be evaluated. For example:
= IFNA({1}, SUM(EXPAND(1,5000,5000,1)))Again, there will be a noticeable calculation lag in this case, not because the value argument evaluates to #N/A, but because it received an array object ({1} instead of 1).
This important distinction is not well documented and is the primary source of confusion regarding behavioral differences between functions like IF/IFS and CHOOSE/SWITCH.
When in doubt, use the TYPE function to determine if your variable is returning a scalar or an array object. If TYPE returns 64, it is an array object. For example:
= TYPE(10) // returns 1 (number)
= TYPE({10}) // returns 64 (array)
= TYPE("a") // returns 2 (text)
= TYPE({"a"}) // returns 64 (array)
= TYPE(1>0) // returns 4 (logical)
= TYPE({1}>0) // returns 64 (array)
= TYPE(1/0) // returns 16 (error)
= TYPE({1}/0) // returns 64 (array)
= MAP(SEQUENCE(5), TYPE) // returns all 1's (each element is read as a number/scalar)
= BYROW(SEQUENCE(5), TYPE) // returns all 64's (each row is read as a single element array object)I hope that helps. Kind regards.