Basic Concepts
We now consider worksheet functions that take a (lambda) function as a parameter. DERIV, DERIV2, INTEGRAL, BISECTION, BRENT, SECANT, NEWTON, NROOTS, MNEWTON, FMIN, FMAX, and FUNC are examples of such Real Statistics functions. Each takes a function as a parameter based on a cell formula.
The second argument in each of these worksheet functions, R1, is a worksheet cell that contains a formula that represents a function f(x) in one variable. The last argument in each of these worksheet functions contains an optional cell address, Rx, 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).
Similarly, the first argument, R1, in the INTEGRAL2, NEWTON2, MNEWTON2, FUNC2, FUNC2D, F2MIN, F2MAX, GRADIENT2, and HESSIAN2 worksheet functions is the address of a cell that contains a formula that represents a function f(x, y) in two variables. The last two arguments of these worksheet functions contain optional cell addresses for x and y. If omitted these addresses default to the first address and, if necessary, the second address in the formula in R1. This provides lambda functionality for functions in two variables.
Finally, NEWTON3, MNEWTON3, FUNC3, F3MIN, F3MAX, GRADIENT3, and HESSIAN3 reference a function in three variables.
Example for a function in one variable
Let’s use the worksheet function DERIV(x, R1, incr, Rx) as an example. E.g. the formula =DERIV(4,A1,,A2) where cell A1 contains the formula =A2^3+EXP(A2) returns the derivative of the function f(x) = x3+ex at x = 4, which is 3(4)2+e4 = 102.589. Here the second argument contains a cell address that contains a worksheet formula. The last argument specifies that A2 is the cell address that represents x in the function f(x).
The last argument in the formula =DERIV(4,A1,,A2) is optional. If it is omitted then this argument defaults to the first valid cell address in the formula in A1, which is A2 in this example. Thus, the formula =DERIV(4,A1) also returns the value 102.589.
Note that A2 is a dummy variable and can be replaced by any other cell address (except the cell address that contains the formula that defines the function, i.e. A1 in the above example). E.g. if A1 contains the worksheet formula =B10^3+EXP(B10) then the formula =DERIV(4,B10,,B10) or =DERIV(4,B10) will also return the value 102.589.
Cell addresses that represent a function variable can’t contain a dollar symbol (thus A2 or B10 is acceptable, but A$4, $A4, or $B$10 are not). Any address that contains a $ is considered to be a constant (whose value is the value in that cell). E.g. if cell B1 contains the formula =B$3*B2^2 and cell B3 contains the value 4, then the value of the formula =DERIV(3,B1,,B2) is the derivative of the function f(x) = 4x2 at x = 3, which is 24.
We get the same result from the formula =DERIV(3, B1) since B2 is the first valid cell address in the formula in cell B1 (B$3 contains a dollar sign and so is not a valid cell address for this purpose).
Using the LAMB or string versions of the lambda function
The LAMB function described in Real Statistics Lambda Capabilities can optionally be used with any of the worksheet functions described above. E.g. we can use any of the following worksheet formulas to obtain the derivative of f(x) = x3+ex at x = 4 where A1 contains the formula =A2^3+EXP(A2).
- =DERIV(3,LAMB(B1),,B2)
- =DERIV(3,LAMB(B1))
- =DERIV(3,LAMB(B1,B2))
We can also use the string expression version of the lambda function capability that was described in Real Statistics Lambda Capabilities with functions such as DERIV, NEWTON, F2MIN, MNEWTON3, etc. E.g. we can use any of the following worksheet formulas to obtain the derivative of f(x) = x3+ex at x = 4.
- =DERIV(4,LAMB(“x^3+EXP(x)”, “x”))
- =DERIV(4,LAMB(“$x^3+EXP($x)”))
- =DERIV(4, “$x^3+EXP($x)”)
- =DERIV(4, “x^3+EXP(x)”,, “x”)
Other uses of the cell formula version of the lambda function
The cell formula version of the Real Statistics lambda functions can be used with the BYCOLS, BYROWS, MAKESARRAY, REDUCES, MAPS, and SCANS functions described in Real Statistics Lambda Capabilities.
E.g. =BYCOLS(A1:C4, A1, A2), where cell A1 contains the formula =SUM(A2), produces the same value as =BYCOLS(A1:C4, “=SUM(col)”, “col”).
Evaluation function
The EVALS worksheet function described in Real Statistics Lambda Capabilities can also be used with cell formulas. E.g. if cell A1 contains the formula =A2^2+EXP(A3), then the formula =EVALS(LAMB(A1,A2,A3),2,3) or =EVALS(A1,2,3) also produces the value 28.08554.
Note that in these expressions the cell A2 variable is assigned the value 2 and the cell A3 variable is assigned the value 3; i.e. the values are assigned in the order in which the cell addresses appear in the formula in cell A1. If you want to change this order then you need to use the LAMB version of the formula. E.g. if you want to assign 2 to the cell A3 variable and 3 to the cell A2 variable, then you can use the worksheet formula =EVALS(LAMB(A1,A3,A2),2,3). Of course, in this case, you can accomplish the same result by using the formula =EVALS(A1,3,2).
When using a non-English language version of Excel
Some modifications to the rules described on this webpage are necessary for users of a non-English language version of Excel. This is especially important when a comma is used as the decimal symbol.
Click here for more details.
Reference
Microsoft (2021) Lambda function
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67