Page 163 - Excel for Scientists and Engineers: Numerical Methods

140 EXCEL: NUMERICAL METHODS

Figure 7-13. Some results returned by the Integrate custom function.
(folder 'Chapter 07 Examples', workbook 'Integration', sheet 'GaussLegendre Integration Fn')
Early versions of this program returned inaccurate results when the range b - a
was large. The function Integrate illustrates one approach to overcoming this
problem. First, the integral is evaluated over the total range b - a. Then the
interval is divided into two halves and each "panel" is integrated separately. The
sum of the two panels is compared to the previous value. If the difference is
larger than a tolerance value, the interval is divided into quarters, the areas
summed and so on. The process is continued for 10 cycles of iteration (512
panels) or until the area difference is less than a specified tolerance.
Because some functions may require a large number of iterations, there may
be a noticeable delay in calculation. Increasing the value of tolerance should
speed up calculation, but only at the expense of accuracy.

Integration with an Upper or Lower Limit of Infinity
Integrals such as
m
A= [F(x)dx (7-13)
a
can be evaluated by summing the areas of a number of panels covering the range
from x = a to x = a suitably large value. It is to be expected that as x+ cr3 the area
of panel(x) + zero. Thus the integral can be evaluated by summing the integrals
of a series of panels of increasing width (e.g., from 0-1, 1-10, 10-100, etc),
ending the summation when the area of the last panel is suitably small. Manual
adjustment of the panel widths is easily done by inspection of the results. Figure
7-14 shows a typical result.