Page 302 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 12 PARTIAL DIFFERENTIAL EQUATIONS 279

terms add the appropriate boundary value terms to the first and last constant
terms (see the four simultaneous equations following equation 12-29). The
preceding Excel formula uses the following named formulas (they can be
examined by choosing Insert -+ Name -+ Define):
ValuesTableCol =COLUMN()-7

TableValuel =INDIRECT("RC"& ValuesTableCo1,O)

TableValue2 =I NDI RECT("RC"& ValuesTableCol +I ,0)
Ta bleValue3 =I NDI RECT("RC"& ValuesTableCol +2,0)

MaxCol =MAX(COLUMN(constants))
MinCol =MIN(COLUMN(constants))
For readers unfamiliar with the INDIRECT function, INDIRECT(ref-text, a?)
returns a reference specified by a text string. The optional argument a1 specifies
what reference style is used: if a1 is TRUE or omitted, the reference is in Al-
style; if a1 is FALSE the reference is in RlC1-style.
The ValuesTableCol formula returns the column number of the values table
that corresponds to the column in the constants table. This column number is
used in the TableValuel, TableValue2 and TableValue3 formulas to return the
appropriate value from the table of values. (The number 7 in the formula might
have to be changed if columns in the spreadsheet were rearranged.) The MaxCol
and MinCol formulas are used in the IF function in the formula in the constants
table so as to add the boundary value terms to the first and last constant terms.
Vapor Diffusion in a Tube (Again)

This example, using the same data, illustrates the use of a smaller grid size.
The spreadsheet ('Crank-Nicholson 37, not shown here, can be examined on the
accompanying CD-ROM. The x-increment is 2 cm, thus creating a table of
values that is 11 columns wide, including the boundary values, and requiring a 9
x 9 matrix of coefficients.
The spreadsheet employs a single formula for all cells of the coefficients
table:

I
=IF(CoeffFableRow=CoeffFableCol,2+2*f, F(ABS(CoeMab1eRow-
CoeffFableCol)= 1 ,-f ,O))
The formula uses the following named formulas

CoeffFableCol =COLUMN()-MIN(COLUMN(coefficients))
CoeffFableRow =ROW()-MI N( ROW(coefficients))