Page 290 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 12 PARTIAL DIFFERENTIAL EOUATIONS 267

Methods for the solution of equation 12-16 can best be illustrated by
reference to a concrete example.

An Example: Temperature Distribution in a Heated
Metal Plate
A typical example of an elliptic partial differential equation involves the
solution of a steady-state heat-flow problem. For example, if a thin steel plate,
10 x 10 cm, has one of the edges held at 100°C and the other three edges at O"C,
what are the steady-state temperatures within the plate? For simplicity, we
assume that heat is not lost through the faces of the plate.
We subdivide the plate by means of a grid with h = k = 0.5 cm, thus creating
a lattice of size 20 x 20. At equilibrium, heat flows in the x-axis direction into a
lattice element at a rate proportional to the temperature of the adjoining element
in the x-axis, and flows out of the element at a rate proportional to the
temperature of the element. The same is true in the y-axis direction. This model
gives rise to an elliptic partial differential equation of the form of equation 12-2.
The time and the thermal conductivity k of the material do not enter into the
equation.
We will use equation 12-16 to calculate the temperature at each lattice point;
the temperature at a lattice point is the average of the temperatures of the four
surrounding lattice points. Thus we have generated a system of 400
simultaneous linear equations in 400 unknowns. Although most of the terms in a
given equation are zero, the problem is still unmanageable. However, we can
solve the system by an iterative method, as described below.
Figure 12-2 shows part of the spreadsheet used to solve the system; each cell
of the 20 x 20 array corresponds to a lattice point. The formula in cell B6 is
=(B5+A6+C6+B7)/4
You can Fill Down the formula into 20 rows and then Fill Right into 20 columns
to create the 20 x 20 array.
Since cell B6 refers to cell 87 and B7 similarly refers to B6, we have created
a circular reference, a formula that refers to itself, either directly or indirectly. In
fact, the spreadsheet contains a large number of circular references. A circular
reference is usually an error; Excel displays the "Cannot resolve circular
references" error message and puts a zero in the cell. In this case, however, the
circular reference is intentional. We can make Excel recalculate the value in
each cell, using the result of the previous iteration.