Thoughts on Numerical Integration (Part 7): Implementation with Excel

Numerical integration is a standard topic in first-semester calculus. From time to time, I have received questions from students on various aspects of this topic, including:

  • Why is numerical integration necessary in the first place?
  • Where do these formulas come from (especially Simpson’s Rule)?
  • How can I do all of these formulas quickly?
  • Is there a reason why the Midpoint Rule is better than the Trapezoid Rule?
  • Is there a reason why both the Midpoint Rule and the Trapezoid Rule converge quadratically?
  • Is there a reason why Simpson’s Rule converges like the fourth power of the number of subintervals?

In this series, I hope to answer these questions. While these are standard questions in a introductory college course in numerical analysis, and full and rigorous proofs can be found on Wikipedia and Mathworld, I will approach these questions from the point of view of a bright student who is currently enrolled in calculus and hasn’t yet taken real analysis or numerical analysis.

In the previous post in this series, I discussed three different ways of numerically approximating the definite integral \displaystyle \int_a^b f(x) \, dx, the area under a curve f(x) between x=a and x=b.

In this series, we’ll choose equal-sized subintervals of the interval [a,b]. If h = (b-a)/n is the width of each subinterval so that x_k = x_0 + kh, then the integral may be approximated as

\int_a^b f(x) \, dx \approx h \left[f(x_0) + f(x_1) + \dots + f(x_{n-1}) \right] \equiv L_n

using left endpoints,

\int_a^b f(x) \, dx \approx h \left[f(x_1) + f(x_2) + \dots + f(x_n) \right] \equiv R_n

using right endpoints, and

\int_a^b f(x) \, dx \approx h \left[f(c_1) + f(c_2) + \dots + f(c_n) \right] \equiv M_n

using the midpoints of the subintervals. We have also derived the Trapezoid Rule

\int_a^b f(x) \, dx \approx \displaystyle \frac{h}{2} [f(x_0) + 2f(x_1) + \dots + 2f(x_{n-1}) + f(x_n)] \equiv T_n

and Simpson’s Rule (if n is even)

\int_a^b f(x) \, dx \approx \displaystyle \frac{h}{3} \left[y_0 + 4 y_1 + 2 y_2 + 4 y_3 + \dots + 2y_{n-2} + 4 y_{n-1} +  y_{n} \right] \equiv S_n.

Computing any of the above formulas on a hand-held calculator can tax the patience of even the most error-conscious student. Indeed, I prefer that my students, when first learning these concepts, use a spreadsheet instead of a calculator or even a computer program, as I think that the visual layout of the spreadsheet aids in understanding how the formula works. In what follows, I implement the above formulas for the integral \displaystyle \int_1^2 x^9 \, dx using n=10 subintervals, so that h = (2-1)/10 = 0.1. To implement the left-endpoint rule, I enter the labels “x” and “x^9” in cells A1 and B1 of a spreadsheet. I then enter 1 (the left endpoint) in cell A2. In cell A3, I enter “=A2+0.1”, instructing the spreadsheet to add 0.1 to the value in cell A2. Then, instead of typing all of the other values of x_k, I use the fill-down feature to repeat this pattern for cells A3 through A11. In cell B2, I enter “=A1^9”, applying the function f(x) = x^9 to the x-coordinate in cell A2. Again, I use the fill-down feature to repeat this pattern for cells B3-B11. The fill-down feature saves a lot of time! Finally, in cell B13, I enter “=0.1*SUM(B2:B11)”, adding the values in cells B2 through B11 and multiplying the sum by h. The result, 78.6581, is the approximation using the left-endpoint rule with 10 subintervals. Once this is done, the right-endpoint rule can be obtained almost for free. The only change is to change the value of cell A2 from 1 to 1.1. Everything else should automatically update. The midpoint rule is also obtained quickly by changing the value of cell A2 from 1 to 1.05, the midpoint of the first subinterval [1,1.01]. Implementing the Trapezoid Rule requires a little more work. We reset the value of A2 back to 1, the value of the left-endpoint. We also fill down the pattern one extra row (in this case, row 12). To implement the Trapezoid Rule, we have to multiply all function values (except for those at the endpoints) by 2. To implement this, I introduce column C. These weights can be typed by hand, but again the fill-down feature can speed things up. Then, in column D, I multiply the values in columns B and C. For example, the result in cell D2 is obtained by typing “=B2*C2”. Once again, the fill-down feature is used for all rows. Finally, the approximation itself is obtained by typing “=0.1/2*SUM(D2:D12)” in cell D13. After implementing the Trapezoid Rule, Simpson’s Rule is not much more effort. The biggest change is the alternating weights, so that the endpoints have weight 1 while the others oscillate between 4 and 2, ending on 4 on the second-to-last value of x. Again, these could be typed by hand, but it’s easiest to enter 4 in cell C3, 2 in cell C4, and then “=C3” in cell C5. The fill-down feature can take care of the rest of the weights. The Simpson’s Rule approximation is obtained by typing “=0.1/3*SUM(D2:D12)” in cell D13, with a new denominator of 3.

One thought on “Thoughts on Numerical Integration (Part 7): Implementation with Excel

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 )

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.