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.
Integration with GaussInt()
Integration with EvalIntt(), (Simpson's Method)
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Engineering students, you may need multiple programming languages « Paviavio’s Blog
Looking good!
But 1 question: is it also possible to integrate a (user-defined) vba-function instead of a function as defined (as string) in the example sheet? That would be helpful for more complex functions.
LikeLike
Richard – see here:
https://newtonexcelbach.wordpress.com/2010/04/08/calling-a-function-as-a-variable/
Should do what you want!
LikeLike
Doug,
I’ve successfully used Eval.xls to calculate some integrals. However, I cannot obtain outputs for Estimated Error, Number of Loops, and Time, which are part of the Result output. Nothing is presented.I’m obviously not doing something which needs to be done!
Any assistance appreciated.
Graeme
LikeLike
Sorry Doug. I was referring to the Gauss-Legendre integrator in Eval.xls.
LikeLike
Graeme – did you enter as an array function?
You need to:
Enter the function in a cell, which will show just the result.
Select that cell and the next three cells across.
Press F2 (to go int edit mode) then press ctrl-shift-enter
You should then get the additional three values
LikeLike
Thank you Doug. I’m most grateful to you. It all worked as you advised.
Graeme
LikeLike
Doug,
Re the integration parameters range – 3 mandatory and 3 optional. If I specify only the 3 mandatory parameters, it refuses to calculate. If I then edit it and specify all six parameters, all is well. My understanding is that the latter three are optional, and if missing, the VBA code sets default values, but still works fine otherwise.
Then, if I choose to edit the range and (again) specify only the first three parameters, it errors with a statement that says I cannot change range parameters, and refuses to let me exit the cell until it returns back to the six cell range.
In summary, it’s not happy with only three integration parameters.
Could you advise me if this is the intended operation of the integrator. Any changes needed to the VBA code? Perhaps the problem is due to my understanding of the program’s operation.
Graeme
LikeLike
Graeme – thanks for the feedback. I’ll have a look and let you know.
LikeLike
Doug, I may have just found the answer?? Although the latter three parameters are optional, the six-cell range still has to be specified, even if the last three cells are empty…?
Ie, the “optional” aspect refers to the contents of the last three cells, but all six cells still have to be specified in the range??
Best regards,
Graeme
LikeLike
Graeme, yes you are right, the function allowed the cells to be blank, but required all 6 cells to be included in the range.
I have now fixed it so you only need to enter a range 3 cells wide unless you want to change the default parameters. You can download the new version from the link in the post.
Thanks again for the feedback.
Doug
LikeLike
I’m most grateful for your efforts Doug.
Graeme
LikeLike
(Apologies for this long post Doug).
This is NOT a criticism of Eval.xls, but an observation and an initial solution:
To hopefully save others much frustration while trying to diagnose some weird, unrelated, and inconsistent behaviour and responses from Excel when trying to use the Eval.xls program (or so it seemed…):
It appears that Excel’s standard operation is to force all entered function calls into upper case, even if entered in lower case, so that if one enters, say, ‘=asin(0.5)’ into a cell, Excel converts it to ‘=ASIN(0.5)’, as may be observed
Unfortunately, this (normal) behavious of Excel can have a devistating effect on Eval.xls’s usage in some circumstances. Eval.xls appears not to distinguish between, say, the letter ‘A’ when specified as a replaceable parameter, and the letter ‘A’ used in Excel’s functions,
For example, if one happens to use, in the same formula, the letter ‘A’ as a replaceable parameter, and the ASIN function, be prepared for some fun!! Eval.xls does its search for every ‘A’ letter in the formula string for the purpose of substituting the letter ‘A’ with the value specified for it. Thus, it will ALSO replace the ‘A’ in the ASIN text as well, if it was used. Of course, Excel is then not very happy, and gives an error.
Mmmm, could it be a VBA source error, a VBA compile error, an Excel error, the formula does look ok, but let me enter it agin just to be sure, or is it an operating system/Excel artifact??? Excel is NOT happy, and every diagnostic avenue reaches the same end – Eval.xls and Excel will not run together.
So, my initial solution, found purely by chance after quite a number of hours, is to use lower case letters for all replaceable parameters, such as ‘a’, ‘b’, ‘c’, etc, both in the cell formulas themselves, and in the substitution cell ranges. By this means, Excel will never be troubled by the lower case letters used in the formulas, and Eval.xls will never replace an upper case letter in a native Excel function with a value, as it will only be searching for lower case letters.
All fixed. Perhaps there are other ways around this issue Doug?
Thought this may be of benefit to first time users of Eval.xls, which is a marvellous program, and I thank Doug for making it available.
Kind regards,
Graeme
LikeLike
Graeme – constructive criticism is always welcome!
Did you have a look at the original post for the Eval function?:
https://newtonexcelbach.wordpress.com/2008/04/22/evaluate-function/
That includes a brief discussion in the comments with Jon Peltier (who has a similar function on his site), including how to handle the problem of replacing bits of function names. Jon handled it with [] around his replaceable parameters, and I went the other way to you, using lower case for all function names, and starting the replaceable parameters with an upper case letter, or an underscore if I had names starting with the same character. The reason for doing it that way was that it avoided problems with names included within other names (e.g. A and Ba). The underscore becomes necessary with some thing like _A and Apples.
I’ll put a summary of this potential pitfall in the spreadsheet where people are more likely to see it.
LikeLike
Doug,
Thanks for your helpful response. No, I had not seen the original post for the Eval function until your response above. As I noted, my solution was simply an immediate workaround for myself, but also for the benefit of learners like myself.
Still, I can see how several ways of addressing this may be identified… I’ll do some more thinking on it, to mature my own ideas and wishes.
With my thanks
Graeme
LikeLike
Doug,
I’ve done a little research for information connecting the G-L order and the precision of the results, and it appears that, for the “average” function (how many are there really??!!), the order of the integrated Legendre polynomial and the significant digits avaliable should typically be commensurate. Is this an acceptable approach in your experience? I can’t really find much substantive information on this issue, although it’s likely to be a ‘rule of thumb’ anyway…?
For example, in Excel and VBA, where 15 significant digits are employed, perhaps a 16th order integration rule is an appropriate maximum fit. No doubt if the G-L order used with VBA is greatly above this, then accumulated round-off and digit loss could cause the result to wander off very comfortably into no-man’s land, losing accuracy on the way, so I’m seeking to achieve the most accurate results, but no more accuracy(!), for the available digits. Hopefully there is some connection between the appropriate G-L order and the available 15 significant digits.
If this is a reasonable approach, and there are some safe ‘rules’, then I would modify the code to include the roots and weights for up to N=15 or 16, say.
Is there theoretical merit in this approach? Is there a practical view? For me, accuracy is always important in calculations.
I would appreciate your thoughts on this issue.
With thanks
Graeme
LikeLike
Graeme – firstly I’m an engineer, not a mathematician, so please don’t take anything I say on the subject of maths as gospel!
But in reponse to your question anyway, it depends. Working with 10 significant figures (which is usually more than enough for an engineer :)) it seems that best performance usually happens with 8 or 10 Gauss Points, but going to 15 figures 12 points seems to have a definite advantage.
Since each loop doubles the number of subdivisions there is a big penalty for each additional loop, so it is likely that going to more than 12 points would improve the performance still further, working at that precision.
You might like to have a look at the integration functions included in the AlgLib library, which go up to 30 points. I’ll probably write some Excel UDFs using the AlgLib routines at some stage, but I don’t know when.
LikeLike
Thanks Doug.
I’ll add the nodes up to say 16 to the vba code, and have an experiment with a number of test integrals and see how well they converge (or not) for each of the orders from say 8 up to 16.
Perhaps you may have a small set of test integrals which you consider representative for such test cases?
Best regards.
Graeme
LikeLike
Probably better to look at a “real mathematician”s site for good test cases.
You good try The Numerical Methods Guy in the blogroll on the right.
LikeLike
Doug,
I note that the Eval.xls calculation timer sometimes returns a value of zero, ie, when the time period is clearly not zero.
Any thoughts on why this happens, and how to overcome it?
With my thanks for any advice.
Graeme
LikeLike
Graeme – the VBA timer just isn’t very accurate, so if you want accurate relative data you need to do more repetitions.
I think there are more accurate timers available, but I couldn’t find anything specific in a quick search.
Any recommendations anyone?
LikeLike
Doug,
I’ve created a Tanh-Sinh numerical integrator for finite limits as a UDF in Excel VBA. This is the fastest and most accurate VBA integrator I have ever witnessed (not due to me – due to the technique). I’ve included extensive background notes in the VBA source code text.
I think yourself and your visitors will find much interest in this.
I’d be happy to share it. Is there a way to upload the file?
LikeLike
Graeme – sounds good. The easiest way would be to email me (dougaj4) at gmail.
LikeLike
Pingback: Eval.xls and the ALGLIB integration functions. | Newton Excel Bach, not (just) an Excel Blog
Many thanks to all who have contributed to such a useful spreadsheet, I had no idea such things were possible in Excel! I have been trying to integrate some irregularly spaced tabulated data that does not fit to any easy function, or interpolate easily, and I was wondering if there was a variant of your spreadsheet that could perform the Gaussian quadrature on this sort of data?
LikeLike
Karen – if your data is suitable for fitting a spline curve that’s probably the best way to go. You can fit a cubic spline to the data then extract the cubic functions for each segment and do an “exact” integration.
There are two spreadsheets which have functions that will do this automatically. Probably the best if you just want to get the results is:
http://interactiveds.com.au/software/CSpline2.zip
See the “Area” sheet for a function that will do what you want, with some examples. More details at:
https://newtonexcelbach.wordpress.com/2009/12/16/cubic-spline-update/
and
https://newtonexcelbach.wordpress.com/2010/05/05/numerical-solutions-with-cspline/
and
https://newtonexcelbach.wordpress.com/2010/05/14/cubic-hermitic-and-cardinal-splines/
An alternative using the Alglib library is in:
http://interactiveds.com.au/software/AL-Spline-Matrix07.zip
or
http://interactiveds.com.au/software/AL-Spline-Matrix03.zip
These also have integration functions that let you select your data and they return the area under the fitted curve. See https://newtonexcelbach.wordpress.com/2010/06/07/alglib-spline-functions/ for more details.
Please ask if you need any help with these, or if they don’t suite your data.
LikeLike
Pingback: Daily Download 17: Numerical Integration | Newton Excel Bach, not (just) an Excel Blog