This webpage describes Real Statistics worksheet functions that implement the gradient and Hessian matrix for functions with two or three variables in Excel.
These worksheet functions take a lambda function parameter. Such functions are described in Real Statistics Lambda Capabilities and Lambda Functions using Cell Formulas.
Evaluation of a Function
The Real Statistics Resource Pack provides the following worksheet functions that evaluate a function in one, two, or three variables, where the function f is expressed as a worksheet formula in cell R1. The variables x, y, and z are represented by the addresses in Rx, Ry, and Rz, respectively.
If the Rx argument is omitted, then x is represented by the first cell address in the formula in cell R1. If the Ry argument is omitted, then y is represented by the second cell address in the formula in cell R1. Similarly, if the Rz argument is omitted, then z is represented by the third cell address in the formula in cell R1.
FUNC(x, R1, Rx) = f(x)
FUNC2(x, y, R1, Rx, Ry) = f(x,y)
FUNC3(x, y, z, R1, Rx, Ry, Rz) = f(x,y,z)
Gradient
The gradient of a function is a column array whose values are the partial derivatives of the function. The Real Statistics Resource Pack provides the following worksheet functions that calculate the gradient of a function in two or three variables.
GRADIENT2(x, y, R1, incr, Rx, Ry): 2 × 1 array containing the gradient of f(x,y)
GRADIENT3(x, y, z, R1, incr, Rx, Ry, Rz): 3 × 1 array containing the gradient of f(x,y,z)
Rx, Ry, and Rz are as described above. incr (default .000001) is used to calculate the partial derivatives (as described for DERIV in Numerical Differentiation).
The Real Statistics Resource Pack also provides the following worksheet function that calculates the gradient of a multivariate function.
GRADIENT(R0, R1, incr): returns column array containing the gradient of f(X). Here f is a multivariate function defined by the expression in R1 and R0 is a column array or cell range containing the values for X.
Hessian
The Hessian matrix of a function is a square matrix whose values are the second-order partial derivatives of the function. The Real Statistics Resource Pack provides the following worksheet functions that calculate the Hessian matrix of a function in two or three variables.
HESSIAN2(x, y, R1, incr, Rx, Ry): 2 × 2 array containing the Hessian of f(x,y)
HESSIAN3(x, y, z, R1, incr, Rx, Ry, Rz): 3 × 3 array containing the Hessian of f(x,y,z)
Rx, Ry, and Rz are as described above. incr (default .000001) is used to calculate the partial derivatives (as described for DERIV in Numerical Differentiation).
References
Wikipedia (2021) Gradient
https://en.wikipedia.org/wiki/Gradient#:~:text=The%20gradient%20of%20H%20at,or%20grade%20at%20that%20point.&text=The%20gradient%20can%20also%20be,by%20taking%20a%20dot%20product.
Wikipedia (2021) Hessian matrix
https://en.wikipedia.org/wiki/Hessian_matrix