Computing Integral of a Function in Excel

Syntax

=QUADF(f, x, a, b, [options])


Use QUADF to compute a proper or improper integral a b f x d x using highly accurate adaptive algorithms. With optional arguments, you can override the default integration algorithm as well as supply singular points for the integrand f(x) if applicable.

QUADF can be nested to compute multiple integrals of any order.

Required Inputs

f a reference to the integrand formula.

If your integrand is too complex to represent by formulas, you can code it in a VBA function (see Example 6).

x a reference to the variable of integration.

a the integral lower limit.

b the integral upper limit.

To input infinite limits use the string "INF" or "-INF" (case insensitive).

Optional Inputs

ctrl a set of key/value pairs for algorithmic control as detailed below.

Description of key/value pairs for algorithmic control

KeyALGOR
Admissible Values (String)QNG,QAG,QAGS,QK15,QK21,QK31,QK41,QK51,QK61
Default ValueQAG
Remarks
  • If singular points are supplied, the algorithm defaults to internal QAGP
  • If an infinite limit is specified, the algorithm defaults to internal QAGI
KeysATOL, RTOL
Admissible Values (Real)>= 0
Default ValueATOL = 1.0E-7, RTOL = 1.0E-5
Remarks
  • Result is reported once either ATOL or RTOL is satisfied.
  • To satisfy ATOL specifically set RTOL to zero and vice versa.
  • ATOL and RTOL cannot be set to zero simultaneously.
  • ATOL and RTOL are not applicable for fixed-order QKn
KeyMAXDIV
Admissible Values (Integer)>= 1
Default Value10000
RemarksNot applicable for algorithm QNG or fixed-order QKn
KeyGKPAIR
Admissible Values (Integer)
  1. (7-15)
  2. (10-21)
  3. (15-31)
  4. (20-41)
  5. (25-51)
  6. (30-61)
Default Value2
RemarksApplicable only for algorithm QAG

spts list of known removable singular points for the integrand.

1 2 2 x 2 - x + 3 2 x 2 d x = 9 - ln 2

Solution

We define the integrand formula in A1 using X1 as variable. Excel reports the error #DIV/0! for the formula in A1 since X1 is undefined. This error can be ignored since X1 serves only as a dummy variable for the integrand function and its value is irrelevant for the integration computed in A2.

A
1=(2*X1^5-X1+3)/X1^2
2=QUADF(A1,X1,1,2)
A
1#DIV/0!
28.306852819
0 1 log x x d x = - 4

Solution

  • We define the integrand formula in A3 using X1 as variable. Excel reports the error #NUM! for the formula in A1 since X1 is undefined. This error can be ignored since X1 serves only as a dummy variable for the integrand function and its value is irrelevant for the integration computed in A4.
  • We use QUADF optional parameter number 5 to specify the QAGS algorithm for better accuracy given that there is a singularity at 0.
A
3=LN(X1)/SQRT(X1)
4=QUADF(A3,X1,0,1,{"Algor","QAGS"})
A
3#NUM!
4-4
0 1 | x - 17 | - 1 7 | x - 23 | - 11 20 d x

Solution

The integrand in this example has two singularities at 1 7 and 11 20 . We make use of optional argument 6 to pass the singular points which are defined in vector B5:B6. Note that we skip over optional argument 5 since we do not alter any defaults.

AB
5=ABS(X1-1/7)^(-1/4)*ABS(X1-2/3)^(-11/20)=1/7
6=QUADF(A5,X1,0,1,,B5:B6)=11/20
A
52.032940662
64.253687688

We could pass the singular points in argument 6 using constant array syntax {0.1428571, 0.55}, but we would lose some accuracy rounding 1/7.

0 1 1 + x 2 d x = π 2

Solution

A
7=1/(1+X1^2)
8=QUADF(A7,X1,0,"inf")
A
71
81.570796327
- e -x2 d x = π

Solution

A
9=EXP(-X1*X1)
10=QUADF(A9,X1,"-inf","inf")
A
91
101.772453851

We demonstrate how to integrate a user defined VBA function with QUADF. You can define your own VBA functions in Excel which is quite powerful when your integrand is difficult to define with standard formulas. We will compute the integral

1 2 log ( x+p ) d x , where p is a constant parameter.

Solution

  1. Open Excel and start VBA Editor by pressing Alt+F11
  2. Insert a Module from the Insert Tab, then code the following function:

  3. Save the workbook with the extension xlsm (macro enabled) and close VBA editor
  4. In Sheet1, define your integrand formula as shown in A11.We pass the integration variable and any other parameters the VBA function expects.
A
11=vbMyFunction(X1, 1)
12=QUADF(A11,X1,1,2)
A
110
120.909542505

Your VBA function name must be prefixed with "vb" to be used with ExceLab solvers.

X1 is just a dummy variable for the integrand function. Its value is ignored.

QUADF algorithms are based on the numerical integration package QUADPACK. QUADF default algorithm is QAG. You can override the default algorithm in optional argument 5 with the key ALGOR For example: =QUADF(f, x, a, b, {"algor","qags"}).

QNG
A non-adaptive algorithm which uses fixed Gauss-Kronrod-Patterson abscissae to sample the integrand at a maximum of 87 points. It is suitable for fast integration of smooth functions.
QAG
An adaptive integration algorithm which divides the integration region into subintervals, and on each iteration the subinterval with the largest estimated error is bisected. This reduces the overall error rapidly, as the subintervals become concentrated around local difficulties in the integrand. The integration rule can be set by the GKPAIR key/value pair.
QAGS
Combines QAG with the Wynn epsilon-algorithm to speed up the integration of many types of integrable singularities. It uses 21-point Gauss-Kronrod rule.
QAGP
Applies QAGS taking into account user-supplied locations of singular points.
QAGI
Used for improper integrals. The integral is mapped onto the semi-open interval (0,1] using the transformation x = (1-t)/t. It is then integrated using the QAGS algorithm using a 15-point Gauss-Kronrod rule
QKn
The fixed-order Gauss-Legendre integration routines are provided for fast integration of smooth functions. The n-point Gauss-Legendre rule is exact for polynomials of order 2*n-1 or less. Rules are available for n = 15, 21, 31, 41, 51, 61. (e.g., QK21.)
  • R. Piessens, E. de Doncker-Kapenga,C.W. Ueberhuber, D.K. Kahaner. QUADPACK A subroutine package for automatic integration.Springer Verlag, 1983.
Question or Comment? Email us:
support @ excel-works.com
ExceLab: Transforming Excel into a Calculus Power House
ExceLab functions and methods are protected by USA Patents 9286286, 9892108, 10114812 and pending.
© 2015-2019, ExcelWorks LLC
Boston, USA