Excel for Numerical Integration

J. C. Daly

Here we use Excel to evaluate the integral of x² from zero to an arbitrary upper limit, a. As shown in Figure 1, three columns were used.

The first is the upper limit. This is also the value of x.
The second column is the value of x².
The third is the value of the integral.

Note that a picture is inserted as the header for the third column.

As shown in Figure 1, cell C6 has been named dx. Since cell C6 is selected, the name appears in a box in the upper left of the picture. We can now address the cell as dx rather than C6. The address dx is an absolute address, similar to $C$6. It will not change when we drag the rows down. Notice the error. For an upper limit of 1, the value of the integral should be 1/3 = 0.33333. Excel shows a value of 0.335. This error can be reduced by reducing dx. This will increase the number of rows required, but the accuracy will increase. For the trapezoidal rule, the error varies inversely as the square of the number of points.

Figure 1 Excel evaluation of the integral of x².

Figure 2 shows the formulas used. To show formulas use; Formulas > Formula Auditing > Show Formulas, as shown in Figure 3. The integral is evaluated using the trapezoidal rule. In each row the contribution of a small trapezoid is added to the integral. For example, in cell D11 the term 0.5*(C10+C11)*dx is the area of the trapezoid from x=0 to x=dx.

Figure 2 Formulas used by Excel are shown.

Figure 3 To toggle between formulas and values, use; Formulas > Formula Auditing > Show Formulas