Other Operations
6.1 Introduction
In this chapter, we gather some operations that do not fall easily into the topics covered in other chapters. These items are:
1. Numerical integration 2. Use of logical IF function 3. Histograms
4. Normal error (probability) distributions
5. Calculation of uncertainty propagation in experiments
6. Multivariable linear and exponential regression analysis with LINEST and LOGEST worksheet functions
7. Examples and comparison of regression methods
Examples are given for each topic and some coordination between the use of histograms, cumulative frequency distributions, and the normal error distributions is presented.
6.2 Numerical Evaluation of Integrals
Numerical evaluation of integrals may be performed in Excel by using either the trape-zoidal rule or Simpson’s rule. First, the area under the curve y = ƒ(x) is divided into increments of Δx. In the trapezoidal rule the curve is replaced by a series of straight line segments, and the area of each element under the curve is calculated as a product of the mean height and the width Δx. Thus,
Ai = Δx(yi + yi+1)/2
Taking the variables as ranging from 1 to n in indices, the total area under the curve will be
I = ∫ydx = A = (1/2)(y1 + 2y2 + … + 2yn−1 + yn)Δx
= [(y1 + yn)/2 + ∑yi]Δx (6.1)
7326_C006.fm Page 121 Tuesday, March 7, 2006 6:19 AM
where the sum is carried out from i = 2 to i = n − 1.
If the increments in x are not uniform the elemental areas are
Ai = ymΔxi = (yi + yi+1)(xi+1− xi)/2 (6.2) The total area under the curve is then obtained by summing all the elemental areas.
Simpson’s rule fits a series of parabolas to consecutive sets of three points on the curve such that the area may be calculated from
I = ∫ydx = (y1 + 4y2 + 2y3 + 4y4 + 2y5 + … +2yn-2 +4yn−1 + yn)Δx/3 (6.3) for uniform increments in x. If the area under the curve is divided into an even number of equally spaced values of Δx, the integral becomes
I = {yi + yn + ∑yi[3 + (−1)i+1]} ×Δx/3 (6.4) where the summation is performed from i = 2 to i = n – 1. For an even number of increments in Δx, the number of data points will be odd.
The larger the number of increments in x, the better will be the approximation of the integral.
Example 6.1: Integration of Sine function
The worksheets shown in Figure 6.1 are set up to calculate the integral of sin(x) over the interval of 0 < x < π. The exact value of the integral is equal to -cos(x) evaluated from 0 to π radians, which gives −(−1 − 1), or an exact value of 2.0. The worksheet is arranged to allow for different values of the increment Δx, which is assigned in cell I4.
We will present the results for two Δx increments: π/10 and π/22. In both cases we have an even number of increments, so Equation 6.4 is used for evaluation with Simpson’s rule. In Figure 6.1a, values of x are listed for the 26-increment case in column A, starting with zero and stepping up by increments specified in cell I4. Column B calculates the corresponding values of sin(x).
The first area (or integral) calculation is made using the trapezoidal rule of Equation 6.1. Half the values of y1 and yn are entered in cells C4 and C26, respectively, whereas the other y-values are copied in cells C5:C25 from cells B5:B25. Cell C29 then calculates the sum of the cells C4:C26 multiplied by the Δx increment to yield an area of 1.9966002 (−0.016999% error) as shown in Figure 6.1c.
For the calculation using Simpson’s rule, the “i” index is created in column E and the arguments of the summation of Equation 6.4 are computed in cells F4:F26. Cell C29 then sums the entries of column F and multiplies by Δx/3 in accordance with Equation 6.4.
The result for the calculated area is 2.00000462 (0.000231% error).
The same calculation is made for 10 increments in x, and the results are displayed in Figure 6.1b. Use of fewer increments gives an area of 1.9835235 (−0.823825% error) for the trapezoidal rule and 2.0001095179 (0.005476% error) when Simpson’s rule is applied. In both cases, Simpson’s rule is more accurate than the trapezoidal rule.
Example 6.2: Numerical Integration of Experimental Data
We now examine a hypothetical set of experimental data shown as the variables x and y at the top left of the worksheet of Figure 6.2. The data are expected to follow a linear variation and hence are plotted on a linear x-y scatter graph as shown in Figure 6.2a
Other Operations 123
through Figure 6.2d. The graph in Figure 6.2a is a type 4 scatter graph with straight line segments joining the data points, the graph in Figure 6.2b is a type 3 scatter graph with a computer-smoothed curve joining the points, and the graph in Figure 6.2c is a type 1 scatter graph. The graph in Figure 6.2d is the same as that in Figure 6.2c but with the addition of a linear trendline fit for the data.
FIGURE 6.1
Trapezoidal Rule Simpson's Rule
x y = SIN(x) Index
0 =SIN(A4) =B4/2 1 =B4 Dx= =PI()/22
=A4+$I$4 =SIN(A5) =B5 =E4+1 =B5*(3+(-1)^E5 Pi/22)
=A5+$I$4 =SIN(A6) =B6 =E5+1 =B6*(3+(-1)^E6)
=A6+$I$4 =SIN(A7) =B7 =E6+1 =B7*(3+(-1)^E7)
=A7+$I$4 =SIN(A8) =B8 =E7+1 =B8*(3+(-1)^E8)
=A8+$I$4 =SIN(A9) =B9 =E8+1 =B9*(3+(-1)^E9)
=A9+$I$4 =SIN(A10) =B10 =E9+1 =B10*(3+(-1)^E10)
=A10+$I$4 =SIN(A11) =B11 =E10+1 =B11*(3+(-1)^E11)
=A11+$I$4 =SIN(A12) =B12 =E11+1 =B12*(3+(-1)^E12)
=A12+$I$4 =SIN(A13) =B13 =E12+1 =B13*(3+(-1)^E13)
=A13+$I$4 =SIN(A14) =B14 =E13+1 =B14*(3+(-1)^E14)
=A14+$I$4 =SIN(A15) =B15 =E14+1 =B15*(3+(-1)^E15)
=A15+$I$4 =SIN(A16) =B16 =E15+1 =B16*(3+(-1)^E16)
=A16+$I$4 =SIN(A17) =B17 =E16+1 =B17*(3+(-1)^E17)
=A17+$I$4 =SIN(A18) =B18 =E17+1 =B18*(3+(-1)^E18)
=A18+$I$4 =SIN(A19) =B19 =E18+1 =B19*(3+(-1)^E19)
=A19+$I$4 =SIN(A20) =B20 =E19+1 =B20*(3+(-1)^E20)
=A20+$I$4 =SIN(A21) =B21 =E20+1 =B21*(3+(-1)^E21)
=A21+$I$4 =SIN(A22) =B22 =E21+1 =B22*(3+(-1)^E22)
=A22+$I$4 =SIN(A23) =B23 =E22+1 =B23*(3+(-1)^E23)
=A23+$I$4 =SIN(A24) =B24 =E23+1 =B24*(3+(-1)^E24)
=A24+$I$4 =SIN(A25) =B25 =E24+1 =B25*(3+(-1)^E25)
=A25+$I$4 =SIN(A26) =B26/2 =E25+1 =B26
AREA= =(SUM(C4:C26))*I4 AREA= =(SUM(F4:F26))*I4/3
(b)
Trapezoidal Rule Simpson's Rule
x y = SIN(x) Index
0 0 0 1 0 Dx= 0.314159265
0.314159 0.309016994 0.309017 2 1.236067977 (Pi/10)
0.628319 0.587785252 0.5877853 3 1.175570505 0.942478 0.809016994 0.809017 4 3.236067977 1.256637 0.951056516 0.9510565 5 1.902113033
1.570796 1 1 6 4
1.884956 0.951056516 0.9510565 7 1.902113033 2.199115 0.809016994 0.809017 8 3.236067977 2.513274 0.587785252 0.5877853 9 1.175570505 2.827433 0.309016994 0.309017 10 1.236067977 3.141593 1.22515E-16 6.126E-17 11 1.22515E-16
AREA= 1.9835235 AREA= 2.000109517
7326_C006.fm Page 123 Tuesday, March 7, 2006 6:19 AM
If a linear plot is expected from either physical reasoning or previous experience, then Figure 6.2d may be the preferred vehicle for presentation of the data. It is interesting to perform a numerical integration of the data over the range 0 < x < 5 with increments Δx = 0.5, the same as used for the data increments.
The integration is performed similar to that of the sine function in Figure 6.1 using both the trapezoidal rule and Simpson’s rule. In Figure 6.3a the formulas are displayed, whereas in Figure 6.3b the computed values of the integral in cell C19 for the trapezoidal integration and in cell F19 for Simpson’s rule are shown. The computed values are 24.15 and 23.8666667, respectively. It is interesting to compare these numbers with those obtained by integrating over the trendline of Figure 6.2d.
The trendline is represented by
y = 1.9145x + 0.0318 and the area under the curve by
Area = ∫ydx = [1.9145x2/2 + 0.0318x] = 24.09025
This value compares favorably with the results obtained in the numerical integration shown earlier, and may be the best representation of the data because it is determined by the trendline fit. The smoothed curve in Figure 6.2b is probably unrealistic, and the scatter of the data is best taken into account by the least-squares trendline fit in Figure 6.2d.
FIGURE 6.1 (continued)
(c)
Trapezoidal Rule Simpson's Rule
x y = SIN(x) Index
0 0 0 1 0 Dx= 0.142799666
0.142799666 0.142314838 0.142314838 2 0.569259353 (Pi/22)
0.285599332 0.281732557 0.281732557 3 0.563465114
0.428398998 0.415415013 0.415415013 4 1.661660052
0.571198664 0.540640817 0.540640817 5 1.081281635
0.71399833 0.654860734 0.654860734 6 2.619442936
0.856797996 0.755749574 0.755749574 7 1.511499149
0.999597663 0.841253533 0.841253533 8 3.365014131
1.142397329 0.909631995 0.909631995 9 1.819263991
1.285196995 0.959492974 0.959492974 10 3.837971894
1.427996661 0.989821442 0.989821442 11 1.979642884
1.570796327 1 1 12 4
1.713595993 0.989821442 0.989821442 13 1.979642884
1.856395659 0.959492974 0.959492974 14 3.837971894
1.999195325 0.909631995 0.909631995 15 1.819263991
2.141994991 0.841253533 0.841253533 16 3.365014131
2.284794657 0.755749574 0.755749574 17 1.511499149
2.427594323 0.654860734 0.654860734 18 2.619442936
2.570393989 0.540640817 0.540640817 19 1.081281635
2.713193655 0.415415013 0.415415013 20 1.661660052
2.855993321 0.281732557 0.281732557 21 0.563465114
2.998792988 0.142314838 0.142314838 22 0.569259353
3.141592654 1.01069E-15 5.05347E-16 23 1.01069E-15
AREA= 1.99660022 AREA= 2.000004631
0 5
Other Operations 125