How To Do Trapezoidal Rule In Excel

In a previous commodity I discussed the situation where you lot have a sequence of (ten,y) points and you lot desire to find the area under the curve that is defined by those points. I pointed out that usually y'all need to utilize statistical modeling before it makes sense to compute the area.

Notwithstanding, there is a numerical technique that is very useful for a wide range of numerical integration scenarios, and that is the trapezoidal rule.

The post-obit graph illustrates the trapezoidal rule. Given a fix of points (x₁, y₁ ), (x₂, y₂ ), ..., (x_n, y_n ), with x₁ ≤ x₂ ≤ ... ≤ x_n , the trapezoidal rule computes the area of the piecewise linear curve that passes through the points. You can compute the expanse under the piecewise linear segments by summing the area of the trapezoids A1, A2, A3, and A4. (Sometimes a trapezoid is degenerate and is really a rectangle or a triangle.)

Implementing the Trapezoidal Rule in SAS/IML Software

It is piece of cake to use SAS/IML software (or the SAS DATA step) to implement the trapezoidal rule. The area of a trapezoid divers by (x_i, y_i ) and (x_i+1, y_i+i ) is

( x_i+1 – x_i ) ( y_i + y_i+1 ) / 2

The showtime term is just the width of the subinterval [x_i, ten_i+ane] and the second term is the average of the heights at each finish of the subinterval.

The following user-defined office computes the area nether the piecewise linear segments that connect the points. The function does not bank check that the x values are sorted in nondecreasing order.

proc iml; /**Given two vectors ten,y where y=f(x), this module    approximates the definite integral int_a^b f(x) dx     by the trapezoid dominion.     The vector x is assumed to be in numerically increasing    order so that a=x[1] and b=x[nrow(x)].    The module does not assume equally spaced intervals.    The formula is     Integral = Sum(  (x[i+1] - ten[i]) * (y[i] + y[i+1])/2 )    **/ start TrapIntegral(x,y);    N = nrow(10);    dx    =   10[two:N] - x[one:North-ane];    meanY = ( y[ii:N] + y[1:N-1] )/two;    return( dx` * meanY ); finish;   /** test it **/ 10 = {0.0, 0.two, 0.4, 0.8, 1.0}; y = {0.5, 0.8, 0.9, 1.0, 1.0}; expanse = TrapIntegral(ten,y); impress area;

Notice that the implementation does non require equally spaced points. The width of all subintervals are computed in a single statement and assigned to the vector dx. Similarly, the average of the heights are computed in a single statement and assigned to the vector meanY. The summation of all the areas is and then computed by using a dot product of vectors. (Equivalently, the module could likewise return the quantity sum(dx # meanY), just the dot product is the more efficient computation.)

The simplicity of the trapezoidal rule makes it an platonic for many numerical integration tasks. Likewise, the trapezoidal rule is verbal for piecewise linear curves such as an ROC curve. As well, as John D. Melt points out, there are other situations in which the trapezoidal rule performs more accurately than other, fancier, integration techniques.

The trapezoidal rule is not equally accurate as Simpson's Rule when the underlying office is smooth, because Simpson'due south rule uses quadratic approximations instead of linear approximations. The formula is usually given in the instance of an odd number of as spaced points. Go out a annotate to discuss the relative advantages and disadvantages of Simpson'south rule every bit compared to the trapezoidal dominion.

In a futurity blog mail, I volition use the TrapIntegral function to integrate some functions that ascend in statistical data assay.