Page 122 - Excel for Scientists and Engineers: Numerical Methods

Chapter 6

Differentiation

The analysis of scientific or engineering data often requires the calculation of
the first (or higher) derivative of a function or of a curve defined by a table of
data points. These derivative values may be needed to solve problems involving
the slope of a curve, the velocity or acceleration of an object, or for other
calculations.
Students in calculus courses learn mathematical expressions for the
derivatives of many types of functions. But there are many other functions for
which it is difficult to obtain an expression for the derivative, or indeed the
function may not be differentiable. Fortunately, the derivative can always be
obtained by numerical methods, which can be implemented easily on a
spreadsheet. This chapter provides methods for calculation of derivatives of
worksheet formulas or of tabular data.

First and Second Derivatives
of Data in a Table

The simplest method to obtain the first derivative of a function represented
by a table of x, y data points is to calculate Ax and Ay, the differences between
adjacent data points, and use Ay/Ax as an approximation to dy/dx. The first
derivative or slope of the curve at a given data point x,, y, can be calculated using
either of the following forward, backward, or central difference formulas,
respectively (equations 6-1, 6-2, and 6-3).
dY
- N-- AY - Y,+l - Yl (forward difference)
dx Ax x,+~ - x,

(backward difference)

(central difference)

The second derivative, dy/dx2, of a data set can be calculated in a similar
manner, namely by calculating A(Ay/Ax)/Ax.

99