Excel Calculus Functionsby John Papiewski
Microsoft’s Excel spreadsheet program comes with a variety of math functions but does not include calculus in the standard version. If you need this feature, you can install third party math software packages that include calculus functions such as derivatives and integrals. These packages extend Excel’s math capabilities, permitting you to use calculus in spreadsheets. Some of these functions work on equations; others perform calculus on numerical data.
The derivative function in calculus determines the rate of change for a point on a continuous line graph. For example, the graph of y = 1 is a horizontal line; the slope of the line is zero and its derivative is zero. For y = x^2, the graph is a curve and the rate of change is different for all points on the curve. The first derivative of x^2 is 2x, so, for example, the rate of change is 8 when x = 4. To take derivatives of polynomials in an Excel spreadsheet, you set up one column containing the degree of the polynomial terms, such as a0 and a1, and a second column with the coefficients of the corresponding polynomial terms. In a separate cell, you enter a value for the independent variable such as x. An Excel library function evaluates these terms and displays the results of the derivative.
You can take the derivatives of non-polynomial equations using other Excel library functions that evaluate the equation symbolically. You type the equation into one Excel cell, a value for the independent variable in a second cell and the library function in a third cell. The function evaluates the equation and determines if a derivative exists; if it does, the function calculates the value of the derivative for the given value.
Integrals are the mathematical inverse of derivatives. If you take the integral of an equation, then take the derivative of the integral, you end up with the original equation. Graphically, an integral finds the area between the X axis and the graph’s curve. Determining an integral symbolically is more challenging than finding the derivative, although computerized methods get rapid results through numeric calculations. The standard approach is to decompose a graph’s curve into thousands of thin rectangles. The thinner the rectangles, the better they fit the curve. The computer adds the areas of the rectangles together, arriving at a total area for the curve. In Excel, you set up two columns of data, one giving the x-axis location of a graph point, the other providing the y-axis location. An integrating function analyzes the two columns and determines the total area under the graph, solving the integral for the data.
In calculus, a gradient is similar to a derivative in that both indicate the rate of change in a curve. A gradient is a vector having direction and magnitude, or length. The gradient of a mountain’s surface, for example, is long and points upward when the slope is steep. At the mountain’s peak, the gradient points horizontally and has zero length. To find a gradient with an Excel library, you type the equation in one cell and values for the equation’s variables in other cells. You specify the locations of these cells in the gradient function. The function computes the gradient values for the location specified by the variable values.