Vertex42 The Excel Nexus

An air-filled tube 20 cm long allows water vapor to diffuse from a source (liquid water) to a drying chamber, where the vapors are dissipated. Initially the tube is capped so that the vapor cannot escape. The temperature of the tube is held at 30°C. The equilibrium vapor pressure of water at this temperature is 31.8 mm Hg; thus the vapor pressure inside the tube is 31.8 mm Hg. When the cap is removed, the vapor will diffuse toward the drying chamber, where the water vapor pressure is zero. We wish to model the vapor pressure along the length of the tube as a function of time.

The diffusion equation is

dt dx2

where p is the vapor pressure and D is the diffusion coefficient in units of cm2 s_1. For water vapor, D = 0.115 cm2 s'1 at 30°C.

We subdivide the length of the tube into uniform subintervals and calculate the value of the function (here the vapor pressure p) at each interior point. Choosing Ax = 4 yields four x values where the function value needs to be evaluated (at x = 4, 8, 12 and 16 cm) and two boundaiy values where it is known (at x = 0 and 20). Also, using Ax = 4 and r = 1 sets At = 139 seconds.

Using equation 12-28 yields four simultaneous equations in four unknowns, thus:

for* = |
4,t = |
139: |
— Po,139 |
+ |
4p4,139 - |
P8.139 = |
Po,o + |
P8.0 |

— 31.8 |
+ |
4p4,139 - |
P8.139 = |
31.8 + |
31.8 | |||

0 |
+ |
4p4,139 - |
P8,139 = |
95.4 | ||||

for x - |
8 ,t = |
139: |
_ P4.139 |
+ |
4ps,139 - |
P12.139 = |
P4,0 + |
Pi 2,0 |

- P4.139 |
+ |
4p8,139 - |
Pl2,139 = |
63.6 | ||||

for x = |
12, t |
= 139: |
- P8,139 |
+ |
4pi2,139- |
Pl6,139 = |
P8.0 + |
Pi 6,0 |

- P8.139 |
+ |
4pi2,139~ |
Pl6,139 = |
63.6 | ||||

forx = |
16, f |
= 139: |
- Pl2,139 |
+ |
4pi6,139- |
P20.139 = |
Pl2,0 + |
P20,0 |

— Pl2,139 |
+ |
4pi6,139- |
0 = |
31.8 + |
0 | |||

~ Pl2,139 |
+ |
4pi6,139- |
0 = |
31.8 |

For r = 1, the values of the coefficients for the four simultaneous equations are shown in the spreadsheet in Figure 12-8. They are designated c1, c2, c3 and c4 in the table. These coefficients will have different values if a different value of r is chosen. The constants (the values of the right-hand side of the four equations) are also shown in Figure 12-8. The formulas in cells I15:L15 are

A |
B |
C |
D |
E |
F |
G |
H |
! |
J |
K |
L | |

1 |
Time-Dependent Diffusion of Water Vapor | |||||||||||

2 |
(calculated by the Crank-Nicholson method) | |||||||||||

3 |
Vapor pressure of water at 30°C = |
31.8 mm H |
g | |||||||||

4 |
Diffusion coefficient at 30"C, cm/sec2 |
0.115 |
(D) | |||||||||

5 |
Dx, cm |
I |
4 |
(Ox) | ||||||||

6 |
Dt=DxA2/D |
139.1 |
(Dt) | |||||||||

7 |
f |
1 |
(f) | |||||||||

a |
coefficients: |
c1 |
c2 |
c3 |
c4 | |||||||

9 |
4 |
-1 |
0 |
0 |
1 | |||||||

10 |
-1 |
4 |
-1 |
0 | ||||||||

11 |
0 |
-1 |
4 |
-1 | ||||||||

12 |
0 |
0 |
-1 |
4 | ||||||||

13 |
Distance x (au) | |||||||||||

14 |
0 |
4 |
8 |
12 |
16 |
20 |
constants | |||||

15 |
0 |
31.8 |
31.8 |
31.8 |
31.8 |
31,8 |
«.0 |
95.4 |
63.6 |
63.6 |
31.8 | |

16 |
I |
139 |
31.8 |
31.5 |
30.6 |
27.2 |
14.8 |
0.0 |
94.2 |
58.7 |
45.3 |
27,2 |

17 |
S |
278 |
31.8 |
30,4 |
27.6 |
21.3 |
12.1 |
0.0 |
91.2 |
51.7 |
39.7 |
21.3 |

18 |
c » |
417 |
31.8 |
29.0 |
24.8 |
18.6 |
10.0 |
0.0 |
88.4 |
47.7 |
34.8 |
18.6 |

19 |
557 |
31.8 |
27.9 |
23.1 |
16.7 |
8.6 |
0.0 |
66.7 |
44.5 |
31,9 |
16.7 | |

20 |
696 |
31.8 |
27.1 21,8 |
15.4 |
8.0 |
0.0 |
85.4 |
42.5 |
29 8 |
15.4 | ||

21 |
835 |
31.8 |
26.6 |
20.9 |
14.5 |
7.5 |
0.0 |
84.5 |
41.1 |
28.4 |
14.S | |

22 |
974 |
31.8 |
26.2 |
20.3 |
14.0 |
7.1 |
0.0 |
83.9 |
40.2 |
27.4 |
14.0 | |

23 |
1113 |
31.8 |
26.0 |
19,9 |
13.6 |
6.9 |
0.0 |

Figure 12-8. A convenient spreadsheet layout for solving a parabolic PDE by the Crank-Nicholson method. The coefficients matrix is aligned directly above the table of values and the table of constants directly to the right, (folder 'Chapter 12 (PDE) Examples, workbook 'Parabolic PDE', sheet 'Crank-Nicholson 1')

The set of simultaneous equations can be solved by methods described in Chapter 9. In this case the solution was found by the matrix inversion method; the array formula in cells D19:G19 is

16 cm

8 cm

12 cm

1000

1500

Time (t), seconds

Figure 12-9. Chart of the results produced by the spreadsheet in Figure 12-8.

(folder 'Chapter 12 (PDE) Examples, workbook 'Parabolic PDE', sheet 'Crank-Nicholson 1')

A plot of the results, shown in Figure 12-9, indicates that a smaller increment of t is required.

In the preceding example, the parameter r was set equal to 1, which simplifies the equations but also determines the values of t that were used in the calculations. In most cases it will probably be desirable to solve the system at specified values of t. Choosing specific values for Ax and At determines the value of r. The following example, using the same data as Example 12-3, illustrates this.

This example uses formulas that permit the construction of a more general model. In Figure 12-10, the following cells or ranges were defined: D: $G$4; Dx: $G$5; Dt: $G$6; f: $G$7; coefficients: $D$9:$G$12; constants: $J$15:$M$26; values: $C$15:$H$27. The formulas in cells G5:G7 are, respectively,

=D14-C14

=B16-B15

A |
B I |
c I |
0 |
E I |
F |
G |
H I | |

1 |
Time-De pen dent Diffusion of Water Vapor(2) | |||||||

2 |
(calculated by the Crank-Nicholson method) | |||||||

3 | ||||||||

4 |
Diffusion coefficient at 30"C, cm/sec" |
0.115 | ||||||

5 |
Dx, cm |
4 | ||||||

6 |
Dt=DxA2Ci |
50 | ||||||

7 |
f |
0.3594 | ||||||

8 |
C1 |
c2 |
c3 |
C4 | ||||

9 |
coefficients |
2,7188 |
-0,3594 |
0.0000 |
0.0000 | |||

10 |
-0.3594 |
2.7188 |
-0.3594 |
0.0000 | ||||

11 |
I |
0.0000 |
-0.3594 |
2.7188 |
-0.3594 |
si | ||

12 |
0.0000 |
0.0000 |
-0.3594 |
2.7188 |
J | |||

13 |
Distance x (cm) | |||||||

14 |
0 |
4 |
8 |
12 |
16 |
20 | ||

15 |
0 |
31.8 |
31.8 |
31.8 |
31.8 |
31.8 |
0.0 | |

16 |
SO |
31.8 |
31.8 |
31.6 |
30.6 |
23.2 |
0.0 | |

17 |
100 |
31.8 |
31.7 |
31 1 |
28.3 |
18.7 |
0.0 | |

18 |
150 |
31.8 |
31.4 |
30.2 |
26.0 |
16.0 |
0.0 | |

19 |
200 |
31.8 |
31.1 |
29.1 |
24,1 |
14.2 |
0.0 | |

20 |
$ 250 |
31.8 |
30.6 |
28.0 |
22.5 |
12 6 |
0.0 | |

21 |
300 |
31.8 |
30.1 |
27.0 |
21.1 |
11.8 |
0.0 | |

22 |
S 350 |
31.8 |
29.6 |
26.0 |
20 0 |
110 |
0.0 | |

23 |
400 |
31.8 |
29.1 |
25.2 |
19.0 |
10.3 |
0.0 | |

24 |
450 |
31.8 |
28.7 |
24 4 |
13.2 |
9.8 |
0.0 | |

25 |
500 |
31.8 |
28,3 |
23.8 |
17.5 |
9,3 |
0.0 | |

26 |
550 |
31.8 |
27.9 |
23.2 |
16 8 |
8,9 |
0.« | |

27 |
600 |
31,8 |
27 S |
22 8 |
16.3 |
8.6 |
0.0 |

Figure 12-10. A convenient spreadsheet layout for solving a parabolic PDE by the Crank-Nicholson method. The coefficients matrix is aligned directly above the table of values and the table of constants is directly to the right, (folder 'Chapter 12 (PDE) Examples, workbook 'Parabolic PDE', sheet 'Crank-Nicholson 2')

In the coefficients table, the formulas =2+2*f, =-f or 0, were entered in the appropriate cells to create the table.

The constants table employs a single formula:

=f*TableValue1+(2-2*f)*TableValue2+f*TableValue3+IF(COLUMN()= MinCol,f*TableValue1,0)+IF(COLUMN()=MaxCol,f*TabieValue3,0)

where TableValuel, TableValue2 and TableValue3 correspond to the function values on the right-hand side of the general equation 12-27a; the IF function 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"& ValuesTableCol,0)

TableValue2 =INDIRECT("RC"& ValuesTableCol +1,0) TableValue3 =INDIRECT("RC"& ValuesTableCol +2,0) MaxCol =MAX(COLUMN(constants))

MinCol =MIN(COLUMN(constants))

For readers unfamiliar with the INDIRECT function, INDIRECT(ref_fexf, a1) 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 RICl-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.

Was this article helpful?

## Post a comment