25.5 Doing Flux (Surface) Integrals on a Spreadsheet

When a surface S is described in parametric form, that is, you are given functions x(s, t), y(s, t) and z(s, t), and a region of values for s and t, you can use a spreadsheet to obtain the flux integral of any spreadsheet definable vector function w (that is the dot product of w with the normal to the surface) over it.

You can do so by exploiting the ability of the spreadsheet to copy a single instruction to a large array of locations in two dimensions, row and column.

Let us suppose you want to integrate over s first and then t, and you will integrate from t₁ to t₂, and from s₁(t) to s₂(t). Suppose you want to break each range into N, N = 100 pieces.

The plan for this is as follows: you set up arrays of s values, t values and then x, y and z values and then w_x, w_y and w_z values, and finally an array for the integral; each of these arrays will be N + 1 by N + 1 in size, but can be constructed by constructing at most 3 or 4 entries. The rest is all copying.

Set up an array of t values, assigning t₁ to the first row that you use in the first column, and (previous + (t₂ - t₁) / N) in the next row beneath it, and (2 * previous - (previous to previous)) beneath that. Copy that into the next 98 rows. Then set the next column entry to "= entry to left" ; copy that into the next 98 columns and rows.

This should give a single constant t value in each row, but different ones in each column.

Next set up two columns that compute s₁(t) and s₂(t) for each t value.

Set up an array of s values by putting = s₁ in the first column, first row = entry to left + (s₂(t₀) - s₁(t₀)) / N (use dollar signs on the N) in the next column, and = (2 * left - leftleft) in the next column.

Then copy this down 98 rows, and copy the third column 98 rows across.

Set up the x, y, z and w_x, w_y and w_z arrays again of the same size, by putting x(s₀(t₀), t₀) in the top left entry of the x array, and copying it into the entire array. This can be done similarly for the other 5 arrays.

To compute the integral you can add up the contribution from each "pair of intervals" ds and dt, that is

w_x * (dy_sdz_t - dy_tdz_s) + w_y * (dz_sdx_t - dz_tdx_s) + w_z * (dx_sdy_t - dx_tdy_s)

To compute this for every 2x2 rectangle in the arrays again you need only compute it for the top left one and copy again to a 100 by 100 array.

This can be done in one step or once for each term. The integral will be the sum of this over the entire array.

What do you do to get w_x or dy_s or dz_t for a pair of intervals?

Suppose the interval corners are (s, t), (s + d, t), (s, t + d') and (s + d, t + d').

Then the analogue of the trapezoid rule in two dimensions gives

Though it is messy to create this last array, it really only has to be done in one square and copied, and also only has to be done once, and can be reused with different integrals, if the other arrays have the same start points.

Notice that to change the domain for s and t you need only change the first two arrays; to change the parametrization you need only change the x, y and z arrays, and to change the vector w whose flux integral you are computing you need only change its three arrays.

You can get integrals over subsets of the surface by taking different sums over the last array.

Here is an example

t₁ =1, t₂ = 2, s₁ = t², s₂ = t³,

Notice that changing t₁, t₂, s₁, s₂, x, y, z, w_x, w_y or w_z each require changing only one entry, and then copying it into the entire corresponding array. (Or wherever appropriate. In the case of t₁ and t₂ you need copy nothing, and for s₁ and s₂ that is a column.)

It is quite easy to extrapolate these results: you can change N to 50 and sum over the top left quarter of the last array, and change N to 25 and sum over the top left quarter of that array.

The results for this integral are 132.2450414 for N = 100, 132.1941481 for N = 50, and 131.9909671 for N = 25.

The difference between these is roughly 4 times smaller between 50 and 100 than it is between 25 and 50. Multiplying the better by and subtracting the worse multiplied by gives 132.262006 and 132.261875. Extrapolating assuming that the remaining error goes down by a factor of 16 gives an estimate of 132.2620145, which is probably accurate to 6 decimal places.

This can be verified by enlarging the arrays and trying N = 200, something which requires only copying, and forming one new sum.

Exercises:

25.6 Create the spreadsheet just described and verify or disprove the claims above.

25.7 What is the best estimate you can get for the integral described?

25.8 Change s and t to go from 0 to and 2, make x, y and z equal what they are in spherical coordinates, and let w be the vector (x, y, 0). Find the integral by this approach to some reasonable accuracy.