Introduction
Excel 365 has introduced a new LAMBDA worksheet function that allows users to include a function as a parameter in another worksheet function. It also adds the BYROW, BYCOL, MAKEARRAY, SCAN, MAP, and REDUCE worksheet functions that take advantage of the new LAMBDA function. See LAMBDA Functions for more details.
Overview
The Real Statistics Resource Pack provides comparable functionality for all users of Excel starting with Excel 2010 via the LAMB worksheet function. Note that, unlike LAMBDA, often such functionality can be achieved without explicitly using LAMB (as described below).
LAMB(expression, parameter_list): returns a string that represents the function defined by the specified expression on the specified list of parameters.
Here, expression is a text string that describes a function. This function can take a numerical value such as “=ABS(x)/y^x”, a Boolean value such as “=OR(x,y)”, a string value such as “=CONCAT(x,y)” or an array value such as “=MDETERM(x)*MMULT(x,y)”. Each of these is a function of two variables (called parameters). These parameters, “x” and “y” in the above examples, can take numeric, Boolean, string, or array values. Note that in each of these expressions the “=” symbol is optional.
In each of the above examples, the parameters take the same type of value as the expression. This is not always so. E.g. the expression in “x<y” takes a Boolean value, but the parameters take numeric values.
The expression can reference up to 10 parameters.
Real Statistics offers two types of lambda capabilities: (1) string expressions and (2) cell formulas.
Lambda capabilities via string expressions
We now give an example of one of the new Real Statistics worksheet functions where R1 is an m × n array or cell range and expression and col are text strings. Here, expression can be viewed as a function of the string in col.
BYCOLS(R1, LAMB(expression, col)): returns a 1 × n row array each of whose elements are the values obtained by applying the function defined by expression to each of the columns in R1.
For example, the formula =BYCOLS(A1:C4, LAMB(“=SUM(col)”, “col”)) returns a 1 × 3 array whose values are the sums of the values in each of the three columns in A1:C4. This is equivalent to the Real Statistics formula SUMCOL(A1:C4).
Note that col can be replaced by any legal name in Excel using any alphanumeric characters or the underscore character “_” and the result will be the same. Thus, =BYCOLS(A1:C4, LAMB(“=SUM(x1)”,”x1″)) will return the exact same result.
The equivalent Excel 365 worksheet function is BYCOL(R1, LAMBDA(parameter, expression)). Thus, we obtain the same result for the above example by using the formula =BYCOL(A1:C4, LAMBDA(col, SUM(col))). Note that in the Excel version, the expression must follow the parameter (or parameter list when there is more than one parameter). The expression and parameter elements are also not inserted inside quotes.
In the Real Statistics version of these worksheet functions, the LAMB function doesn’t necessarily need to be explicitly referenced. Thus, we can use the formula =BYCOLS(A1:C4, “=SUM(col)”, “col”) to achieve the same result.
Furthermore, the parameters also do not need to be explicitly stated. E.g. the worksheet function =BYCOLS(A1:C4, “=SUM($col)”) will return the same result. In this case, the parameter name must be preceded by a dollar symbol.
Lambda functions via string expressions
Real Statistics Functions: The following functions provided by the Real Statistics Resource Pack are the equivalents of the Excel 365 LAMBDA functions described in Lambda-based Functions.
- BYCOLS(R1, expression, col)
- BYROWS(R1, expression, col)
- REDUCES(init, R1, expression, value, accumulator)
- SCANS(init, R1, expression, value, accumulator)
- MAKESARRAY(nrow, ncols, expression, row, col)
- MAPS(R1, expression, element)
Note that the order of the value and accumulator parameters in REDUCES and SCANS is the reverse of these parameters in Excel’s REDUCE and SCAN.
As described above, if we use parameter names that are preceded by $ then we don’t have to explicitly include the parameter names in the worksheet formula. E.g. =MAKESARRAY(2, 3, “=$i^$j”) will create a 2 × 3 array whose first row contains the values 1, 1, 1 and whose second row contains the values 2, 4, 8. Here the first parameter found in the expression, namely $i, corresponds to row. The second parameter, namely $j, corresponds to col. Note too that =MAKESARRAY(2, 3, “=$j^$i”) will produce the same array, whereas =MAKESARRAY(2, 3, “=j^i”, “i”, “j”) will produce an array whose first row is 1, 2, 3 and whose second row is 1, 4, 9.
Evaluation of Lambda functions
Real Statistics Functions: The Real Statistics Resource Pack supplies the following worksheet function.
EVALS(expression, value_list): returns the value of the lambda function represented by expression at the specified list of values.
E.g. =EVALS(LAMB(“x^3+EXP(y)”, “x”, “y”), 2, 3) returns the value of 2^3+EXP(3), namely 28.08554. Here, expression is a lambda expression created using LAMB. When only default parameters are used, i.e. those beginning with a $, then LAMB may be dropped. Thus, we get the same result via the formula =EVALS(“$x^3+EXP($y)”, 2, 3).
The following worksheet function is similar to EVALS, except that the list of values takes the form of a column array.
EVALArray(expression, value_array): returns the value of the lambda function represented by expression at the specified column array value_array.
You can specify value_array as a cell range with one column. Alternatively, you can use an expression of the form {2; 3} where the semi-colon is used to indicate a new row. For example, =EVALArray(“$x^3+EXP($y)”,{2;3}) returns the same value as =EVALS(“$x^3+EXP($y)”, 2, 3).
The Real Statistics Resource Pack also supports the following worksheet function.
EVALFunc(lambda_expression, value_list): returns the value of the lambda function represented by lambda_expression at the specified list of values.
EVALFunc is similar to EVALS, except that the expression must be of form LAMB(…) and the value_list must be enclosed in set brackets. E.g. =EVALFunc(LAMB(“x^3+EXP(y)”, “x”,”y”), {2, 3}) also returns 28.08554.
Lambda functions using cell formulas
Worksheet functions such as DERIV, INTEGRAL, BISECTION, NEWTON, FMIN2, and GRADIENT3 are also examples of Real Statistics lambda functions. For example, the second argument in the DERIV(x, R1, incr, Rx) worksheet function, R1, is a worksheet cell that contains a formula that represents a function f(x) in one variable. The last argument, Rx, is an optional cell address that represents the variable x of the function. If omitted this address defaults to the first address in the formula in R1. R1 and Rx play a similar role to the LAMBDA worksheet function (or the explicit or implicit Real Statistics LAMB worksheet function).
Check here for more information about how to use these worksheet functions.
More about EVALS and EVALFunc
EVALS, EVALArray, and EVALFunc formulas can be used with both string and cell expressions. Examples are shown in Figure 1.
Figure 1 – Numeric examples
The arguments for EVALS can be numeric (as in Figure 1) as well as strings and arrays (as shown in Figures 2 and 3). EVALArray supports numeric and string values. EVALFunc only supports numeric arguments.
Figure 2 – String examples
Figure 3 – EVALS array examples
Partial Lambda Function
The worksheet formula LAMB(expression, parameter_list) essentially defines a function that is used by other functions. E.g. if expression has k+1 variables, say f(x0, …,xk), then this function will also have k+1 variables. For some m < k and constants am+1, …, ak we can also define a new function g(x0, …,xm) = f(x0, …, xm, am+1, …, ak). This is essentially what the worksheet function LAMBX does.
LAMBX(expression, value_list): returns a string that represents the function defined by the specified expression where the final variables are evaluated at the specified list of values.
Here, expression is a text string that describes a function. E.g. if the expression is “=ABS($x)/$y^$x”, then LAMBX(“=ABS($x)/$y^$x”), 3) represents the function g(x) = ABS(x)/3^x. Here 3 replaces the second variable in the expression, namely y.
The advantage of using LAMBX is that we can use the worksheet formula =LAMBX(“=ABS($x)/$y^$x”), A1) where cell A1 can contain any value for y we like. This approach is especially advantageous when LAMBX is used with INTEGRAL or other functions that call functions.
Graphing a function y = f(x)
You can also use Real Statistics’ lambda capability to specify a function f(x) in one variable, and then create a graph of y = f(x). Click here for details.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
Reference
Microsoft (2021) Lambda function
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67