Numerical Integration Using Excel

  1. Introduction
  2. Finding the area under a curve is a useful tool in a large number of problems in many areas of science, engineering, and business. Mathematically, this is integration. For a curve produced by a function, you may be able to integrate the function from a to b and calculate the area under the curve in that way. However, for curves produced from data, or for curves that are produced by some complicated functions, analytical integration may not be possible. In these cases, the most common way to find the answer is to perform the integration "numerically". This can be done a number of ways, but one easy way is to employ MS Excel.

  3. Overview
  4. Consider the function shown below. How could we calculate the area under the graph from the origin to b?

    Mathematically, the area under f(t) from 0 to b is given by the following operation:

    When we do not know the function f(t), or we cannot evaluate it analytically, we need to approximate the above integral numerically. This can be done in a number of ways, some of which are better than others. The trapezoidal rule is one way to calculate this integral that is (1) easy to implement; (2) quite accurate; and (3) quite robust. The idea is to break the function up into a number of trapezoids and calculate their areas:

    The area of the shaded trapezoid above is

    The area under the graph is therefore the sum of the trapezoids.

    The accuracy of the numerical integration will go up with decreased spacing between the time points. In fact, if you halve the time-spacing of your data (i.e., take twice as many measurements of the same length of time), the accuracy of the numerical integration will go up by a factor of 4.

  5. Details Using Excel

We provide an example spreadsheet implementing this numerical integration (using the trapezoidal rule) with example data. The example contains a number of text boxes to help you understand what is being done. You may save this spreadsheet and copy its design to integrate your own function.

Roy Haggerty

November 19, 1999