Update 27 Nov 2018: Version 4.41 now available from the link below, with minor modifications for compatibility with Excel 2007.
I recently received a new version (4.4) of the numerical integration spreadsheet by Graeme Dennes which is now available for download from:
The new spreadsheet includes significant improvements in the performance of several functions, as well as new functions:
The Tanh-Sinh quadrature workbook has been enhanced as follows:
(1) A faster Tanh-Sinh program has been implemented, increasing the speed by around 50 percent, and the speed of the DE programs has been doubled.
(2) A fast finite interval program TINT has been added. It runs at over twice the speed of the Gauss-Kronrod program.
(3) The speed of the Gauss-Kronrod program has been improved through modifications developed by Berntsen, Espelid and Sorevik.
(4) The Plotter worksheet now shows two plots: the plot of the selected function over the finite interval (a,b), and the plot of the function after being transformed by the Tanh-Sinh function.
(5) Now includes over 1200 test integrals with results correct to 15 significant digits. This may be the largest set of diverse test integrals and results available at no cost. It includes several of the “standard” sets of test integrals in wide use.
(6) The Romberg integrator, written by the author, may be the fastest and most accurate Romberg integrator available. Advice to the contrary would be most welcome.
(7) Minor change to allow compatibility with Excel 2007.
Graeme Dennes
Newton Excel Bach, not (just) an Excel Blog 
When I click on “Click to Show Charts” in any of the worksheets I get an error About PrtSafe :
‘ High Resolution Timer:
‘ Set up access to the Counter value of the high performance timer
Private Declare PtrSafe Function GetCounter Lib “kernel32” Alias “QueryPerformanceCounter” (ByRef x As Currency) As Long
Excel 2007
LikeLike
Hi Paris,
I will have a look at the VBA code.
Thank you for letting me know.
Graeme Dennes
LikeLike
Graeme has updated the code for compatibility with Excel 2007, and the new version is now available from the link at the top of the post.
LikeLike
Hi Paris,
I’ve made a small VBA code change which hopefully resolves the compatibility problem you experienced. I’d be very pleased to know if the updated version “behaves” itself under Excel 2007.
Thank you for your feedback with this issue.
LikeLike
Thank you Graeme. Works Fine Now.
:{P
LikeLike
I downloaded “Tanh-Sinh Quadrature by Graeme Dennes.zip”, extracted the two files and then tried to open the xlm file and got the following error: “Excel found a problem with one or more formula references in this worksheet.” Note that by default my system first opens up an xls* file from the internet in “Protected View” should this have any bearing on the problem.
I’m using Microsoft Excel for Office 365 MSO (16.0.10730.20102) 32-bit.
LikeLike
Hi Paul,
I believe the issue is caused by a change in the environment, ie, when the workbook is run on a different computer, even when run in a different folder on the same computer, from that on which it was last saved. I don’t know the technical basis for the message, but perhaps someone else may. However, the solution is simple.
1. Open the workbook with Excel.
2. When the reported error message is displayed, click Ok to close the message box.
3. Click the Enable Content button (if presented) to activate the macros in the workbook. (The workbook will not function as intended with macros disabled.)
4. Save the workbook (this is most important).
5. Close Excel.
6. Open the workbook with Excel again. This time, it should open without displaying the error message. (If it does show the error message, repeat the steps above one more time.)
7. If requested to enable Content, do so, and the workbook should run as intended.
8. All should be correct now.
If Excel opens the workbook in Protected View (with all active content such as macros disabled), then the Protected View will have to be disabled (part of step 3 above) to allow the workbook to run as intended.
Let me know if this resolves the issue for you.
Graeme Dennes
LikeLike
Thank you,
Yes, this appears to have resolved the Excel problems that I had before.
However, I’ve noticed problems for the code within the cells of the columns titled “Abs. True Error”.
1) It appears that the code attempts to calculate Relative True Error’s instead of Absolute True Errors as the column is named.
2) Isn’t the following true:
a) Absolute True Error = ABS( True Value – Approximate Value )
b) Absolute Relative True Error = ABS( ( True Value – Approximate Value ) / True Value )
c) The Relative Error is undefined when the True Value is zero as it appears in the denominator.
3) The code incorrectly has the Relative True Error (if I’m correct in 1 above) as being:
ABS(ABS(True Value)-ABS(Approximate Value))/ABS(True Value))
This would incorrectly reduce the error calculated should one of the values be positive and the
other negative as I have seen in a few of the examples.
( i.e. sheet Tanh-Sinh equation 405: x/SIN(x)/COS(x) ).
4) The code also incorrectly returns the value for Absolute True Error (I assume) as being
ABS(ABS(True Value)-ABS(Approximate Value))
when the True Value is equal to zero! Same problem as in 4 above.
Paul
LikeLike
Hi Doug,
My apologies for the long reply below.
Hi Paul,
I’m pleased to know the workbook opening error was resolved.
Thank you for detailing your findings regarding the “Abs True Error” calculation in the workbook. All of your points are correct. I’ll try to describe how the calculations came about. Unfortunately, it’s not a simple issue, but rather, a quite complex one.
As background, the Abs True Error figure is my attempt to represent the magnitude of the difference between the Exact Integral number in column G and the calculated Integral number in column H, both numbers being floating point numbers and similar in value. All numbers within Excel are held in binary format, described by the IEEE 754 numeric data standard for floating point computation. (Nearly all hardware and software vendors use this standard.) Within Excel, the number type “Double” is a ‘binary64’ formatted number which uses 64 binary bits to represent numbers and provides 15 (decimal) significant digits (SD) of precision. The binary64 format accommodates numbers in the range of approx. ±10^-308 to ±10^308, including zero.
In Scientific number format, if all 15 digits and signs are the same and the exponent digits and signs are the same, we would say the numbers are identical, and so the absolute difference between them should be zero. Unfortunately, this doesn’t always result in practice with floating point numbers. As an example, the result provided by integrand #144 in cell H159 of the T-S sheet is 156.079666010823 to 15 SD, which is identical to the exact result shown in cell G159, yet if we calculate the Abs True Error by ABS(G159-H159), then it should show the result as zero. Unfortunately, it shows a result of 2.56E-13. As another example, for the integrand #147, which also shows identical numbers for the true and calculated values, when we calculate the Abs True Error as above, it shows 5.55E-17. Then there is #151 which also shows equal numbers for the true and calculated values, but now the Abs True Error shows the expected result of zero. (At last!) All these examples use the same calculation in their Abs True Error cells, yet return different Abs True Error figures when we expect them all to return zero!
After much experimentation, the present Abs True Error formula contains a mixture of “workarounds” to provide a (hopefully) practical/meaningful/useful result which is either exact, nearly exact or is a “best shot” in regard to the two numbers at hand. The cell formula evolved from my numerous attempts at combating the (undesirable) findings. I would check each integral result in turn, and if the returned Abs True Error figure was not correct, I attempted to identify the cause of the problem and a way to “correct” it. It wasn’t the result of any number theory. Each different “type” of issue, and there was a range of issues found throughout the worksheet results, required its own “solution”, and the present calculation in the Abs True Error cells is the latest incarnation. Relative error calculations were also necessary in some situations, as you’ve identified. Another set of issues arises for numbers close to zero and for numbers close to unity. The present formula for Abs True Error is used throughout the entire workbook.
The problem at hand presents itself whenever we wish to determine the difference between two floating point numbers which are either identical or perhaps differ in say the last (15th) place, when we are at the very edge of the “resolution” of the binary/decimal calculations and representations.
The following two links provide some background on the broader issues involved with floating point numbers:
https://stackoverflow.com/questions/328475/should-we-compare-floating-point-numbers-for-equality-against-a-relative-error
https://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/
The following links refer to the IEEE 754 standard:
https://en.wikipedia.org/wiki/IEEE_754
https://en.wikipedia.org/wiki/IEEE_754-1985
I hope the above links can provide some background and guidance for dealing with something as innocuous as floating point numbers. The second link above by Bruce Dawson is particularly instructive, and may be one of the best information resources available on the subject.
A small number of the Correct Digits numbers in the workbook are also not correct. This calculation is also interesting in its derivation because of the range of issues which have to be accommodated, in conjunction with the Abs True Error figure. For example, if the two numbers differ by 1 to 9 units in their 15th place, then 14 digits are correct, etc. The Correct Digits cells utilise the Abs True Error figures.
In summary, the Abs True Error and Correct Digits formulas required some “modifications” (trickery) to ensure “reasonable” results were provided for all integrals, even if those results were not strictly correct. The perfect calculation is not known to me. The underlying issue we’re dealing with is not mathematical per se, but rather the manipulation of the decimal representation of binary numbers.
I hope I’ve been able to add some meaning, if not answers, to this issue, and how the current calculations came to be the way they are. Unfortunately, it’s a quite imprecise issue in a practical sense. The safest way to deal with 15 SD floats is to “put up a wall” by only allowing say the first thirteen digits to be “exposed” and discard the remaining two digits by applying rounding or truncation rules. Quite simply, with floats, we cannot rely on the usual calculation of the difference between two numbers when the difference lies in the least significant (15th) digit, or even perhaps, the 14th digit.
Graeme Dennes
LikeLike