﻿
 UFDC Home |   Help  |   RSS

 Title: Water Resources Analysis Using Electronic Spreadsheets, Table 4: Lotus Model of the Puls Flood Routing Method
CITATION THUMBNAILS PAGE IMAGE ZOOMABLE
Full Citation
STANDARD VIEW MARC VIEW
 Material Information Title: Water Resources Analysis Using Electronic Spreadsheets, Table 4: Lotus Model of the Puls Flood Routing Method Physical Description: Photograph Language: English
 Subjects Spatial Coverage: North America -- United States of America -- Florida
 Notes Abstract: Water Resources Analysis Using Electronic Spreadsheets, Table 4: Lotus Model of the Puls Flood Routing Method General Note: Box 7, Folder 1 ( Vail Conference 1987 - 1987 ), Item 90 Funding: Digitized by the Legal Technology Institute in the Levin College of Law at the University of Florida.
 Record Information Bibliographic ID: WL00000697 Volume ID: VID00001 Source Institution: Levin College of Law, University of Florida Holding Location: Levin College of Law, University of Florida Rights Management: All rights reserved by the source institution and holding location.

Full Text

Table 4. Lotus Model of the Puls Flood Routing Method.

Column
A B C 0 E F

G H I J

Stage-outflow 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 sub-file
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
Stage-volume relationship
Volume-stage 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 ac-ft

Outflow
cfs ac-ft

Sum
Outflow
ac-ft

Stored
Volume
ac-ft

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 acre-feet. 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)1-5 .....................(5)

where Q = outflow at weir, cfs,K = weir coefficient, St = present

stage, feet, St-1 = 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*(A35-A34)*3600/43560,I34) .............. (6)

where F35 = location of outflow in time step t, cfs, A35-A34 =

location of time sum at time step t minus location of time sum at

time step t-l, hours, and 134 = location of stored volume in time

step t-l, acre-feet. As always, the locations represent the

actual values found in those locations. The @MIN function is one

of numerous built-in 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 t-1 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- '+SRt-SRt-l ....................... (7)

19

5-.

where SVt = stored volume at time step t, ac-ft, SVt-1 = stored

volume at time step t-1, ac-ft, Qt-l = outflow at time step t-1,

ac-ft, SRt = sum runoff at time step t, ac-ft, and, SRtl = sum

runoff at time step t-l, ac-ft.

Finally, the last column, stage, is calculated by using the

@LOOKUP command. The volume-stage 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. 1-2-3 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 acre-feet 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

^. /