A previous post presented a user defined function (UDF) to perform a numerical integration of a function specified as a text string.  The UDF allowed the number of subdivisions of the integration range to be specified, but the number of subdivisions required to reach the required precision of the result had to be found by trial and error.

The Numerical Methods Guy has recently posted a method of performing the integration with the number of subdivisions doubled at each successive stage, until the required precision is reached.  The beauty of this approach is that all the calculations used in the earlier stages are used in the final solution, so the calculation time is virtually the same as if the minimum required number of subdivisions had been known at the outset.  I have used this approach to adapt the Excel UDF EvalInt(), the new function being EvalIntT, which is included in Eval.zip.   As usual, the download includes full open source code.

 The Numerical Methods Guy’s posts cover only the trapezoidal rule, but the same approach has been applied using Simpson’s Rule, which will normally give a much faster solution.  Examples of the output of EvalIntT are shown below:

EvalIntT Function Results

The results shown are for the example given at The Numerical Methods Guy blog, and are virtually identical to the results given by the Matlab function listed there.  Note that using Simpson’s Rule the function has given a result to the specified precision with 512 subdivisions, compared with 32768 for the trapezoidal rule, and the solution time is over 60 times faster.

When entering the function data take care when the function text includes any of the paramaters, as in this case x is included in “exp”.  This problem is avoided by entering the function in lower case and the paramaters in upper case, so the correct substitutions are made when the function is evaluated.