Numerical Differentiation

Basic Approach

We can approximate the derivative of a function f(x) by

for a small positive value of h.

Worksheet Functions

The following worksheet function takes a lambda function parameter. Such functions are described in Real Statistics Lambda Capabilities and Lambda Functions using Cell Formulas.

Real Statistics Function: The Real Statistics Resource Pack provides the following function that calculates the derivative of any smooth function.

DERIV(x, R1, h, Rx) = the derivative f ′(x) where R1 is a cell that contains a formula that represents the function f(z) and z is the value contained in cell Rx. If Rx is omitted, then it defaults to the first cell referenced in R1. h is the increment value; default .000001.

Note that the formula in cell R1 can’t refer to any cells that in turn refer to cell Rx (or the first cell referenced in R1 if Rx is omitted).

Basic Example

Example 1: Find the derivative f’(4) for the function

The result is shown in two ways in Figure 1. First, f(x) is written in Excel format in cell B5. This function references two cells, namely B3 and B4. For the purposes of the description of the function, B3 is the variable x and $B4 contains the constant 3. Note that a reference to a constant must contain a $ (i.e. use absolute referencing).

We know by calculus that

and so f′(4) = 1/4 + 2(4) = 8.25, as shown in cell B6.

Figure 1 – DERIV function

Cell B8 shows the same result using Real Statistics’ DERIV function. This is made clearer in cell B9 where the constant 4 is used as the first argument. If we wanted to find f′(2), we could have used the formula =DERIV(2,B5,,B3). We don’t need to change the value in cell B3 to 2. The DERIV function only cares about the address of cell B3, not its value.

Since the formula in cell B5 only contains one variable reference, namely B3 ($B4 is a reference to a constant), we can replace the formulas in B8 and B9 by =DERIV(B3,B5) and =DERIV(4,B5), respectively to obtain the same result.

Distribution Example

Example 2: Find the pdf value at x = 4 for the gamma distribution with parameters alpha = 5 and beta = 6 using the DERIV function.

We can find the answer directly, as shown in cell B16 of Figure 2.

Figure 2 – Gamma distribution pdf

We also see that the result can be calculated using the formula in cell B18 since the pdf f(x) is the derivative of the cdf F(x), i.e. f(x) = F′(x). We see that since B12 is the first (and only) cell address referenced in the formula in cell B15 that doesn’t contain a $, we can get the desired answer without employing an explicit fourth argument. Thus, we get the same result using the formula =DERIV(4,B15) or =DERIV(B12,B15,,B12) or =DERIV(4,B15,,B12).

Observation: We note that the default value for the third argument in the DERIV formula, incr = .000001, has yielded quite accurate results for the derivative. This value can be changed if necessary.

Second Derivative

Real Statistics Function: The Real Statistics Resource Pack provides the following function.

DERIV2(x, R1, h, Rx) = the second derivative f ′′(x) where R1 is a cell that contains a formula that represents the function f(z) and z is the value contained in cell Rx. If Rx is omitted, then it defaults to the first cell referenced in R1. h is the increment value; default .000001.

As described above, this worksheet function takes a lambda function parameter. More information about such functions can be found at Real Statistics Lambda Capabilities and Lambda Functions using Cell Formulas.

Gradient and Hessian Matrix

Click here for a description of the Real Statistics worksheet functions that calculate the gradient and Hessian matrix for functions with two or three variables. The gradient and Hessian matrix are the multivariate versions of the first and second derivatives.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage.

Reference

Wikipedia (2021) Numerical differentiation
https://en.wikipedia.org/wiki/Numerical_differentiation