Numerical integration with on-sheet calculations

Following a comment here I have prepared a spreadsheet that works through the Tanh-Sinh Quadrature process with on-sheet calculations.  For that purpose Graeme Dennes has provided a simplified version of the code:

QUAD_RODRIGUEZ_TANH_SINH for finite intervals.
Translated from HP RPN code written by Cesar Rodriguez.
This is the second fastest T-S program I have found, second only to the program by Michalski and Mosig.
This is also the shortest T-S program I have found!
No subroutines. Two nested Do loops.

The spreadsheet calculations and the new VBA code have been added to the zip file with the full set of integration routines, which can be downloaded from:

Tanh-Sinh Quadrature

(Link updated 12th July 2020)

Screenshots from the new spreadsheet are shown below, and the download files include unprotected spreadsheet calculations, and full open source VBA code.

The VBA version of the Quad-Rodriguez_Tanh-Sinh function can be used in the same way as the other functions by Graeme, either evaluating functions entered as text on the spreadsheet, or using the built-in VBA functions:

The spreadsheet calculations on the next sheet show the VBA code in Column A, with the corresponding spreadsheet calculations to the right.  The three grey shaded cells allow user input of any function of a single variable and the integration limits:

The bulk of the work is done in two nested do loops, with the outer loop repeated four times, and the inner loop up to 15 times, with the spreadsheet calculations in Columns G to AO in the two screenshots below.  Click on the images for a larger view, or download the spreadsheet to follow the calculations in detail:

The results from each of the four outer loops are shown below, with increasing precision from each loop.  The final loop returns the exact value of pi to 15 significant figures (the greatest precision available in Excel), and agrees exactly with the VBA function results:

Note that if a different function is entered in cell G4 the spreadsheet calculations will be updated, but the “actual error” figures will not be correct, since they assume an integration with a result equal to pi.  Also there may be small differences between the VBA and on-sheet results, since the VBA adds iterations where necessary to reach the required precision, but the on-sheet calculations have a fixed number of iterations.

This entry was posted in Excel, Maths, Newton, Numerical integration, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.