Table 4. Lotus Model of the Puls Flood Routing Method.
Column
A B C 0 E F
G H I J
Stageoutflow relationship.
Minimum weir crest, ft.=
Assume a max. stage, ft.
Max. discharge rate, cfs,
Discharge formula.
Q=K*h'1.5
Thus, K=Q/h'1.5= 1
11.9
15.5
132 9 H= 3.6
9.33
Flow routing through system.
Basis:
25 year rain, Inches= 10
Ground storage, S, inches= 4.5
Acreage= 625
Rain distribution, see subfile
SCS runoff,R=(P.2*S"2)/(P+.8*S)
Weir elevation, ft= 11.9
Weir equation, Q=X*h'1.5
K 19.33
Stagevolume relationship
Volumestage relationship
Sum
Time Rain
Hours Ratio
0 0
24 0.146
48 0.359
58 0.572
59 0.628
59.5 0.678
59.75 0.828
60 1.015
60.5 1.088
61 1.126
62 1.177
72 1.359
96 1.472
120 1.568
144 1.568
168 1.568
192 1.568
216 1.568
240 1.568
Sum
Runoff
inch acft
Outflow
cfs acft
Sum
Outflow
acft
Stored
Volume
acft
Sum
Rein
inch
1.46
3.59
5.72
6.28
6.78
8.28
10.15
10.88
11.26
11.77
13.59
14.72
15.68
15.68
15.68
15.68
15.68
15.68
0.06
1.02
2.51
2.95
3.35
4.60
6.24
6.90
7.25
7.71
9.39
10.45
11.36
11.36
11.36
11.36
11.36
11.36
0.0
0.0
9.2
2.7
1.7
1.3
2.0
5.0
5.2
10.7
116.1
282.1
167.2
9.3
0.0
0.0
0.0
0.0
0.0
0.0
9.2
11.9
13.6
14.8
16.9
21.9
27.1
37.8
153.9
436.0
603.2
612.5
612.5
612.5
612.5
612.5
Stage
Ft
11
11.06
11.95
13.23
13.42
13.67
14.46
15.24
15.37
15.42
15.50
15.81
15.56
13.58
11.00
11.00
11.00
11.00
11.00
18
s,/
Columns F and G calculate the outflow at the weir both in cubic
feet per second and acrefeet. Column F uses the weir equation
presented at the top of the page by averaging the present stage
with the previous stage:
Q = K*(((St+St_I)/2)WE)15 .....................(5)
where Q = outflow at weir, cfs,K = weir coefficient, St = present
stage, feet, St1 = stage in previous time step, feet, and, WE =
weir elevation, feet. The outflow is the minimum of the outflow
calculated by the previous column, or the stored volume
calculated by the previous time step. This is accomplished
through use of the @MIN function (@MIN(a,b)), and is represented
at 120 hours as follows:
@MIN (F35*(A35A34)*3600/43560,I34) .............. (6)
where F35 = location of outflow in time step t, cfs, A35A34 =
location of time sum at time step t minus location of time sum at
time step tl, hours, and 134 = location of stored volume in time
step tl, acrefeet. As always, the locations represent the
actual values found in those locations. The @MIN function is one
of numerous builtin formulas that perform spreadsheet calcula
tions.
Column H is calculated by adding the summed outflow of the
previous time step to the outflow of the current time step
(Column H, Row t1 plus Column G, Row t). The stored volume is
calculated as the maximum of zero or the result of the following
equation:
SVt = SVt.1 Qt '+SRtSRtl ....................... (7)
19
5.
where SVt = stored volume at time step t, acft, SVt1 = stored
volume at time step t1, acft, Qtl = outflow at time step t1,
acft, SRt = sum runoff at time step t, acft, and, SRtl = sum
runoff at time step tl, acft.
Finally, the last column, stage, is calculated by using the
@LOOKUP command. The volumestage relationship table, presented
in Table 5, is located elsewhere on the same spreadsheet. Since
stage is a function of storage, it can be interpolated by
calculating the stored volume, as done in Column I, and
consulting the table. The @VLOOKUP function is used to
automatically look up the corresponding stage value in the table
when given the storage value. 123 also has an @HLOOKUP command
to be used for looking up a value from a row.
The @VLOOKUP command is specified as follows:
@VLOOKUP(x ,range,offset) .................. ..... (8)
where x = cell address of known value, range = range where table
is found, and offset = number of columns to the right of the x
range to look. For the case of Table 5, the command used to find
the corresponding value for a storage of 100 acrefeet would be
@VLOOKUP(100,T349..U359,1) .................... (9)
meaning the value is 100, the range of the table is between cells
T349 and U359, and the corresponding value is found one column to
the right.
If interpolation is required, then the supplied value is
rounded to the nearest value found in the table. In order to
avoid this error and be able to interpolate between values, the
20
^. /
