Excel LAMBDA Function

With the advent of Excel’s LAMBDA function, the question arises of how it interacts with @RISK.

To summarize LAMBDA and its use, typically one defines an Excel Name, e.g. 'MyLambda', with the cell definition of

=LAMBDA(x,y,x+(y*$A$1))

In another cell one can enter the formula

=MyLambda(B3,7)

and the formula will return the same as

=B3*(7*$A$1)

This formula can be entered in many cells, so the same user-defined function can be re-used. This is all transparent to @RISK, until the user decides to put an @RISK function in the LAMBDA definition, e.g. define an Excel Name 'RiskLambda' as

=LAMBDA(x,RiskUniform(0,x))

If the user puts a reference to RiskLambda in 10 cells, there will be 10 evaluations of that formula on each iteration of the simulation. @RISK currently has no way to track the data from those calls, or to apply random number seeds to those calls in a consistent fashion. What happens is that the RiskUniform draws a random Monte Carlo (not Latin Hypercube) sample each call, and that sample is never saved in the simulation results, and so, for example, sensitivity analysis cannot analyze the effect of that sample on an output.

In addition, since @RISK cannot effectively seed those samples, running the simulation multiple times will give different results, even with the same seed. This is similar to how using an Excel 'rand()' function would lead to non-reproducible simulation results. Note, this behavior is the same as for @RISK functions defined in Excel Names even before LAMBDA, so in that sense nothing has changed except the temptation to put @RISK functions in Names has increased because of the utility of the LAMBDA feature.

Palisade has no plans to change this behavior.