Appendix A

Computer Programs and Resources for this model

Contents

| Return to Lab homepage |

Spreadsheet Programming:

Sample Results:
 
 
       
paste from cell D21 or D22
paste from cell D24,D25, or D26
calculating soil(n)
  
row/column
L
M
N
O
P
Q
R
S
T
                   
10
 day(n)
sat(n)
sot(n)
dw(n)
evap(n)
prec(n)
rawsoil(n)
soil(n)
runoff(n)
11
 0
250.0
275.0
1.000
0.075
0.175
1.500
1.500
0.000
12
 1
250.0
275.4
0.100
0.008
0.018
1.510
1.510
0.000
13
 2
250.0
275.9
0.101
0.008
0.018
1.521
1.521
0.000
14
 3
250.0
276.3
0.101
0.008
0.018
1.531
1.531
0.000
15
 4
250.1
276.7
0.102
0.008
0.019
1.543
1.543
0.000
16
 5
250.1
277.1
0.103
0.008
0.019
1.554
1.554
0.000

 
A
B
C
D
E
F
G
H
I
J
1
 Lab 2 - Modeling Soil Runoff (Feedback systems)        
2
 This worksheet calculates the water content and flows for a piece of ground
3
   given certain precipitation, run-off, and evaporation rates.    
4
 Notes                  
5
 The data extends down to row 1471 so that a 4 year period is covered (1460 days).
6
 See procedure for a description of the constants and variables      
7
 DON'T PRINT OUT the data -- it's 58 PAGES; all the graphs are on the first 4 pages.  
8
                    
9
                    
10
 Constants     Variables (vary daily)        
11
 pi
3.14
   sat(n)            
12
 wfc
15
   sot(n)            
13
 a
25
   dw(n) field efficiency        
14
 b
275
   evap(n) evaporation rate      
15
 aa
0.005
   prec(n) precipiration rate      
16
 bb
0.2
   soil(n) soil water content      
17
 bbb
0.18
   runoff(n) run-off rate        
18
 dt
1
                
19
                    
20
 rawsoil(0)
1.5
   Alternate Formulas for dw(n) (see column O)  
21
      
1.000
 dw(n)=1        
22
      
0.000
 dw(n)=soil(n-1)/wfc (only for O12 and below)
23
                    
24
       Alternate Formula for prec(n) (see column Q)  
25
      
0.000
 prec(n)=dw(n)*0.175      
26
      
0.175
 prec(n)=0.175        
27
      
0.000
 prec(n)=dw(n)*(aa*(sot(n)-b)+bbb)  

 
 
 

Formulas used to calculate the sample results shown above:
 
 
day(n) sat(n) sot(n) dw(n) evap(n) prec(n) rawsoil(n) soil(n) runoff(n)
0 =(($B$13)*SIN((2*PI()*L11/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L11/365)) + $B$14 1 =O11*($B$15*(M11-$B$14)+$B$16) =O11*($B$15*(N11-$B$14)+$B$17) =$B$20 =IF(R11<0,0,IF(R11>$B$12,$B$12,R11)) =IF(R11>$B$12,R11-S11,0)
=L11+$B$18 =(($B$13)*SIN((2*PI()*L12/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L12/365)) + $B$14 =S11/$B$12 =O12*($B$15*(M12-$B$14)+$B$16) =O12*($B$15*(N12-$B$14)+$B$17) =R11+(Q12-P12)*$B$18 =IF(R12<0,0,IF(R12>$B$12,$B$12,R12)) =IF(R12>$B$12,R12-S12,0)
=L12+$B$18 =(($B$13)*SIN((2*PI()*L13/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L13/365)) + $B$14 =S12/$B$12 =O13*($B$15*(M13-$B$14)+$B$16) =O13*($B$15*(N13-$B$14)+$B$17) =R12+(Q13-P13)*$B$18 =IF(R13<0,0,IF(R13>$B$12,$B$12,R13)) =IF(R13>$B$12,R13-S13,0)
=L13+$B$18 =(($B$13)*SIN((2*PI()*L14/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L14/365)) + $B$14 =S13/$B$12 =O14*($B$15*(M14-$B$14)+$B$16) =O14*($B$15*(N14-$B$14)+$B$17) =R13+(Q14-P14)*$B$18 =IF(R14<0,0,IF(R14>$B$12,$B$12,R14)) =IF(R14>$B$12,R14-S14,0)
=L14+$B$18 =(($B$13)*SIN((2*PI()*L15/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L15/365)) + $B$14 =S14/$B$12 =O15*($B$15*(M15-$B$14)+$B$16) =O15*($B$15*(N15-$B$14)+$B$17) =R14+(Q15-P15)*$B$18 =IF(R15<0,0,IF(R15>$B$12,$B$12,R15)) =IF(R15>$B$12,R15-S15,0)
=L15+$B$18 =(($B$13)*SIN((2*PI()*L16/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L16/365)) + $B$14 =S15/$B$12 =O16*($B$15*(M16-$B$14)+$B$16) =O16*($B$15*(N16-$B$14)+$B$17) =R15+(Q16-P16)*$B$18 =IF(R16<0,0,IF(R16>$B$12,$B$12,R16)) =IF(R16>$B$12,R16-S16,0)
=L16+$B$18 =(($B$13)*SIN((2*PI()*L17/365) - PI()/2)) + $B$14 =($B$13)*SIN((2*PI()*L17/365)) + $B$14 =S16/$B$12 =O17*($B$15*(M17-$B$14)+$B$16) =O17*($B$15*(N17-$B$14)+$B$17) =R16+(Q17-P17)*$B$18 =IF(R17<0,0,IF(R17>$B$12,$B$12,R17)) =IF(R17>$B$12,R17-S17,0)

 
 
Alternate Formulas for dw(n) (see column O)
1 dw(n)=1
=H21/$B$12 dw(n)=soil(n-1)/wfc
   
Alternate Formula for prec(n) (see column Q)
=B25*0.175 prec(n)=dw(n)*0.175
0.175 prec(n)=0.175
=B27*($B$15*(A27-$B$14)+$B$17) prec(n)=dw(n)*(aa*(sot(n)-b)+bbb)

 

| Appendix A contents | Return to Lab homepage |

FORTRAN code:

The FORTRAN code is available on request from Richard T. Wetherald or Jimmy Tung.
 

| Appendix A contents | Return to Lab homepage |
b
The Woodrow Wilson National Fellowship Foundation
CN 5281, Princeton NJ 08543-5281 - Tel:(609)452-7007 - Fax:(609)452-0066
Technical contact: lpt@woodrow.org