Computing Numerical Derivative from a Set of (x,y) Data Points

Syntax

=DERIVXY(x, y, p, [options])


DERIVXY is a powerful function which employs cubic splines for estimating the derivative at an arbitrary point based on a set of (x,y) data points only.

With options, you can elect to weigh the data points, use exact or smooth least square fit, as well as specify end points slopes if known.

Required Inputs

x a vector of the points x-coordinates.

y corresponding vector of the points y-values.

p the point at which to compute the derivative.

Optional Inputs

n order of the derivative. Enter a value of 1, 2, or 3. Default is 1.

ctrl a set of key/value pairs for algorithmic control as detailed below.

Description of key/value pairs for algorithmic control

KeysISLOPE, ESLOPE
Admissible Valuesreal number
Default ValueUnconstrained
Remarks
  • If defined, the spline curve first derivative will match the supplied value.
  • Cannot be imposed with a linear spline or with PERIOIDC option enabled.
KeyPERIODIC
Admissible Values (Boolean)True or False
Default ValueFalse
Remarks
  • The y-values at the end points must be equal if PERIOIDC is set to True.
  • ISLOPE, ESLOPE constraints are ignored
KeySFACTOR
Admissible Values (real)≥ 0
Default Value0
Remarks
  • A zero value will compute an exact interpolating spline passing through the data points. A large value will compute a least-squares smooth fitting curve.
  • The smoothing factor has a significant effect on the computed integral value.

w strictly-positive corresponding set of weights for the (x,y) data points. Default value is unity.

In this example we sample the function f(x) = x sin(x2) +1 then compute its derivative from the sampled data points using DERIVXY and compare the result to the analytic derivatives given by f'(x) = sin(x2) + 2 x2 cos(x2)

Solution

Using the formulas shown in Table 1, we generate by AutoFill the (x,y) data values and the analytic derivatives at the x-values. The numerical values are shown in Table 2 below:

Table 1
ABC
3x_datay_dataAnalytic derivatives at x values
40=A4*SIN(A4^2)+1=SIN(A4^2)+2*A4^2*COS(A4^2)
50.25=A5*SIN(A5^2)+1=SIN(A5^2)+2*A5^2*COS(A5^2)
6 ⇓ Drag down to row 20
Table 2
ABC
3x_datay_dataAnalytic der
4010
50.251.0156150.1872153
60.51.1237020.7318602
70.751.3999771.4849677
811.8414711.9220756
91.252.2499571.0258913
101.52.16711-2.0487081
111.751.138268-6.0268418
122-0.5136-5.9859515
132.25-1.11352.5335616
142.50.91705212.459939
152.753.6340035.3043158
1632.236355-15.988226
173.25-1.94996-9.773066
183.5-0.0889222.972966
193.754.7395513.0952707
204-0.15161-30.933007

Using DERIVXY formulas shown in Table 3, we generate by AutoFill the numerical derivatives at the x-values and compute the relative error with respect to the analytical derivatives. The numerical results are shown in Table 4 below.

Note that we have passed defined names x_data and y_data for columns A4:A20 and B4:B20 respectively. This is one way to lock the data so AutoFill ignores these arguments and increments only the third argument. Alternatively we could use a dollar sign, $, to lock the first two arguments during AutoFill using the formula =DERIVXY($A$4:$A$20, $B$4:$B$20, A4).

Table 3
EF
3Numerical Derivatives% Errors
4=DERIVXY(x_data,y_data,A4)1
5=DERIVXY(x_data,y_data,A5)=ABS((E5-C5)/C5)
⇓ Drag down to row 20 
Table 4
EF
3Numerical Derivatives% Errors
4-0.006256108100.00%
50.1892893521.11%
60.7335224570.23%
71.4889669280.27%
81.9238378940.09%
91.0154412181.02%
10-2.077937041.43%
11-6.0439622930.28%
12-5.9147912211.19%
132.681877385.85%
1412.355165090.84%
154.8675293588.23%
16-15.993640560.03%
17-7.90051412519.16%
1819.6924186814.28%
199.404971886203.85%
20-58.0646525287.71%

Notice that the errors are largest near the end points of the data since the default spline fit does not constrain the derivatives at the end point. But in this example we know the exact derivatives at the end points from the analytical formula. We can improve the accuracy by supplying this data using the keys ISLOPE and ESLOPE in the optional control argument for DERIVXY. We name the range K1:L2 end_slopes and define the following Key/Value pairs for the start and end slope using the computed analytical derivative values in C4 and C20 from Table 2 above.

KL
1ISLOPE

0

2ESLOPE

-30.933

We regenerate the new derivatives and errors in columns H4:H20 and I4:I20 from the enhanced DERIVXY formulas in Table 5. The numerical values obtained by AutoFill are shown in Table 6 below.

Table 5
HI
3Nummerical Derivative with end slopes% Errors
4=DERIVXY(x_data,y_data,A4,1,end_slopes)1
5=DERIVXY(x_data,y_data,A5,1,end_slopes)=ABS((H5-C5)/C5)
  ⇓ Drag down to row 20
Table 6
HI
3Num. Der. with end slopes% Errors
400.00%
50.1876129660.21%
60.7339718910.29%
71.4888455780.26%
81.9238738590.09%
91.0154187081.02%
10-2.0778829641.42%
11-6.0441560870.29%
12-5.914070121.20%
132.6791867715.75%
1412.365206430.76%
154.8300546358.94%
16-15.8537830.84%
17-8.42246964713.82%
1821.640383215.80%
192.13506929631.02%
20-30.933006690.00%

Notice the improvement in the relative error with the defined end points slopes. Keep in mind that we are working with a relatively small sample size. To obtain any further improvement would require finer sampling.

DERIVXY computes the derivative by fitting a cubic spline model to the data.

  • Paul Dierckx. Curve and Surface Fitting with Splines. Numerical Mathematics and Scientific Computation. Oxford University Press, (1995).
  • Carl de Boor. A Practical Guide to Splines (Applied Mathematical Sciences). Springer, (2001).
Question or Comment? Email us:
support @ excel-works.com
ExceLab: Transforming Excel into a Calculus Power House
ExceLab functions and methods are protected by USA Patents 9286286, 9892108, 10114812 and pending.
© 2015-2019, ExcelWorks LLC
Boston, USA