The previous post on integration provided an Excel User Defined Function (UDF) to perform numerical integration using Simpson’s Rule or the Trapezoidal Rule. A third method is Gaussian Quadrature which is often much more accurate and/or quicker than Simpson’s Rule. The UDF GaussInt() carries out integration using Gaussian Quadrature, in a similar format to the earlier functions, and has been added to the Eval.xls spreadsheet. As far as I know this is the only open source VBA code with this technique available on the Web.
The basis of the method is to find the mean value of the function over the integration range by taking a weighted average of the function value at a number of specified points. Using two integration points provides the same precision as Simpson’s Method using two subdivisions, but increasing the number of integration points increases the precision much more rapidly than a corresponding increase in the number of subdivisions using Simpson’s Method. In addition to the function to be integrated, constant symbols and values, and the variable symbol, the UDF optionally allows the following values to be specified:
- Tolerance (maximum estimated error / integration value) – default 1e-10
- Maximum number of subdivision loops (the number of subdivisions, and approximate execution time, are doubled in each additional loop) – default 10
- Number of integration points – default 8.
The UDF allows for between 2 and 12 integration points. For each different number of integration points two arrays are set up, specifying the position and weight of each point. The values for the required coefficients were taken from: ActiveState Code, and for values up to 8 integration points were checked at: Gauss Legendre Coefficients (link no longer active).
Having set up the coefficient arrays the analysis procedure is quite simple:
- For each integration point: Substitute the position coefficient into the function
- Evaluate the function and multiply by the appropriate weighting
- Sum each integration point to estimate the integration result
- If the required precision has not been achieved, double the number of subdivisions and repeat
As well as the integration result the UDF provides the estimated error, the number of loops required, and the execution time.
The screen shots below show the evaluation of a trigonometrical function (with exact value of 2), and an evaluation of Pi by finding the area under a unit circle between X=0 and X=0.5. The upper screen shot is using the GaussInt function, and the lower one using Simpson’s Rule. It can be seen that the GaussInt function is of the order of 60 times faster, for the same precision, compared with Simpson’s Rule in these cases.