=DERIVF(f, x, p, [options])
Use DERIVF
to compute first or higher order derivatives
$\frac{{d}^{n}f\left(x\right)}{d{x}^{n}}$
of a function f(x) at x=p using highly accurate adaptive algorithm.
With optional arguments, you can specify a higher derivative order, as well as override the default algorithm parameters.
DERIVF
can be nested to compute partial derivatives of any order,
$\frac{\partial}{\partial z}\frac{\partial}{\partial y}\frac{\partial}{\partial x}f\left(x,y,z\right)$
f
a reference to the function formula.
If your function is too complex to represent by nested formulas, you can code it in a VBA function (see Example 3).
x
a reference to the variable of differentiation.
p
the point at which to compute the derivative.
n
the derivative order. You can enter an integer number from 1 to 4. Default is 1.
ctrl
a set of key/value pairs for algorithmic control as detailed below.
Description of key/value pairs for algorithmic control
Key  RTOL 
Admissible Values (Real)  > 0 
Default Value 

Remarks  Convergence is a function of several factors including function metric. Setting RTOL too small may prevent successful convergence. 
Key  INITSTEP 
Admissible Values (Integer)  > 0 
Default Value  0.05 
Remarks  This parameter is very influential. Try different small and large values when encountering convergence difficulties. 
Key  ITRNMAX 
Admissible Values (Real)  >= 3 
Default Value  50 
Remarks  Sets an upper bound on the maximum size of the generated Neville' tableau by Ridders' algorithm. 
Consider the function:
$f\left(x\right)=xsin\left({x}^{2}\right)+1$
The analytic derivatives of f(x) up to order four are:
${f}^{\text{'}}\left(x\right)=sin\left({x}^{2}\right)+2{x}^{2}cos\left({x}^{2}\right)$
${f}^{\text{'}\text{'}}\left(x\right)=6xcos\left({x}^{2}\right)4{x}^{3}sin\left({x}^{2}\right)$
${f}^{\text{'}\text{'}\text{'}}\left(x\right)=6cos\left({x}^{2}\right)8{x}^{4}cos\left({x}^{2}\right)24{x}^{2}sin\left({x}^{2}\right)$
${f}^{\text{'}\text{'}\text{'}\text{'}}\left(x\right)=16{x}^{5}sin\left({x}^{2}\right)80{x}^{3}cos\left({x}^{2}\right)60xsin\left({x}^{2}\right)$
We compute the numerical derivatives of at x = 0 and at x = 1 for orders 1 to 4 and compare them to the analytical values shown in column B of the tables below:
A  
1  =X1*SIN(X1^2)+1 
2  =DERIVF(A1,X1,0) 
3  =DERIVF(A1,X1,0,2) 
4  =DERIVF(A1,X1,0,3) 
5  =DERIVF(A1,X1,0,4) 
A  B  
1  1  1 
2  3.18603E15  0 
3  0  0 
4  6  6 
5  1.27896E15  0 
A  
6  =DERIVF(A1,X1,1) 
7  =DERIVF(A1,X1,1,2) 
8  =DERIVF(A1,X1,1,3) 
9  =DERIVF(A1,X1,1,4) 
A  B  
6  1.922075597  1.922075597 
7  0.124070104  0.124070104 
8  21.27590825  21.27590825 
9  80.24890792  80.24890780 
Consider the partial derivative:
$\frac{\partial}{\partial y}\frac{\partial}{\partial x}cos(x,y)=sin\left(xy\right)xycos\left(xy\right)$
We compute the partial derivative of cos(xy) at (π,π) by nesting DERIVF
and compare the result with the analytical value shown in B3 below:
A  
1  =COS(X1*Y1) 
2  =DERIVF(B1,X1,PI()) 
3  =DERIVF(B2,Y1,PI()) 
A  B  
1  1  
2  0  
3  9.3394486379  9.3394486379 
We demonstrate how to compute the derivative for a user defined VBA function with DERIVF
.
You can define your own VBA functions in Excel which is quite powerful when your function is difficult to define with standard formulas.
For illustration, we compute the derivative for
Insert a Module from the Insert Tab, then code the following function:
A  
11  =vbMyFunction(X1, 1) 
12  =DERIVF(A11,X1,2) 
A  
11  0 
12  0.333333 
Your VBA function name must be prefixed with "vb" to be used with ExceLab solvers.
X1 is just a dummy variable for the function and its value is ignored.
DERIVF
implements Ridders' method which uses an adaptive step to produce a much higher precision than a simple finite differencing method with a fixed step. It employs Neville' algorithm and polynomial extrapolation to drive the step size to zero within machine accuracy.
The starting step size for Ridders's algorithm is an important parameter that can aid successful convergence of the algorithm. You can override the default value of this parameter using the control key
INITSTEP (for example, DERIVF(A1, X1, P1, 1, {"INITSTEP",0.1})
).
The starting step size need not necessarily be small but rather should scale with a range around the point p over which the function changes noticeably. Ridders' method attempts to drive the step size to zero by polynomial extrapolation using Neville' algorithm.