Table 2. Lotus Model, Distanceweighting
Distribution for Rainfall at Cypress
Creek (Partial Listing).
A B C 0 E F G H I J K L H
3 Coordinates
Distance
Weighting
Weights
Weighted
Values
5 1 2 3 4 5 6 7 8 9 10 11 12 13
X
0.5
1.5
2.5
3.5
4.5
5.5
6.5
7.5
8.5
9.5
10.5
11.5
12.5
13.5
14.5
15.5
0.5
1.5
2.5
3.5
4.5
5.5
6.5
DROSE
5.66
5.00
4.47
4.12
4.00
4.12
4.47
5.00
5.66
6.40
7.21
8.06
8.94
9.85
10.77
11.70
5.00
4.24
3.61
3.16
3.00
3.16
3.61
DCYP
6.32
6.08
6.00
6.08
6.32
6.71
7.21
7.81
8.49
9.22
10.00
10.82
11.66
12.53
13.42
14.32
5.39
5.10
5.00
5.10
5.39
5.83
6.40
DLEO
15.62
14.87
14.14
13.45
12.81
12.21
11.66
11.18
10.77
10.44
10.20
10.05
10.00
10.05
10.20
10.44
15.00
14.21
13.45
12.73
12.04
11.40
10.82
ROSE
4.10
5.87
7.99
9.92
10.75
9.92
7.99
5.87
4.10
2.80
1.89
1.26
0.83
0.54
0.00
0.00
5.87
9.20
14.03
19.46
22.13
19.46
14.03
CYP
3.06
3.46
3.61
3.46
3.06
2.54
1.99
1.51
1.10
0.79
0.55
0.00
0.00
0.00
0.00
0.00
4.97
5.81
6.14
5.81
4.97
3.93
2.95
LEO
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.52
0.00
0.00
0.00
0.00
0.00
0.00
O.CO
0.00
0.00
0.00
SUMDIST
7.16
9.33
11.59
13.38
13.81
12.46
9.98
7.37
5.20
3.58
2.43
1.26
1.35
0.54
0.00
0.00
10.84
15.01
20.18
25.27
27.10
23.39
16.98
WR
0.57
0.63
0.69
0.74
0.78
0.80
0.80
0.80
0.79
0.78
0.78
1.00
0.62
1.00
0.00
0.00
0.54
0.61
0.70
0.77
0.82
0.83
0.83
WC
0.43
0.37
0.31
0.26
0.22
0.20
0.20
0.20
0.21
0.22
0.22
0.00
0.00
0.00
0.00
0.00
0.46
0.39
0.30
0.23
0.18
0.17
0.17
WL
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.38
0.00
0.00
0.00
0.00
0.60
0.00
0.00
0.00
0.00
0.00
Notes:
1. Col.uns 1 and 2 are the maps coordinates for each node.
2. Col.mns 3 through 5 are the distances between each
grid node and each raingage, in miles.
3. Columns 6 through 8 are unnormalized weighting factors,
computed by applying the weighting formula:
((1(D/(1.1*DMAX)))'2)/((D/(1.1*DMAX))'2)
4. Column 9 is the sum of columns 6 through 8.
5. Columns 10 through 12 are the normalized weighting factors
for each node.
6. Column 13, labeled WEIGHT, is the weighted rainfall
estimation for each node.
Actual Gage Rainfall
inches
Rose
Cypress
St. Leo
Average
2.42
2.03
1.25
1.74
11
5:/la
WEIGHT
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.4
2.0
2.4
2.4
2.4
2.2
2.3
2.3
2.3
2.3
2.4
2.4
more complicated formula developed for the Surface II (Sampson,
1978) mainframe contouring software is used here, i.e.,
W = ((l(D/(1.1*DMAX)))2)/((D/(1.1*DMAX))2)....... (1)
where W = rain gage weight, D = distance from point to rain gage,
and DMAX = distance from point to farthest rain gage. The third
through fifth columns, labeled DROSE, DCYP, and DLEO, are the
distances in miles between each grid cell and the rain gages. For
instance, the Rose raingage is located at grid cell X = 4.5 and Y
= 4.5, which is found in cells A78 (X) and B78 (Y). The formula
used to calculate the first value for DROSE (located in C10)
would therefore be
((($A$78A10)2) + (($B$78B10)2))1/2 .............(2)
or the equivalent of
((X1X2)2 + (YlY2)2)1/2 ......................... (3)
This formula is entered into the first cell and copied into the
remaining cells. The spreadsheet automatically changes each
formula's variables to fit the needs of each cell. For example,
in equation 2, the reference cells, A78 and B78 should be held
constant. This is achieved using the $ prefix so that they are
absolute addresses. The other cells, A10 and B10, will change as
the formula is copied to other cells. Thus, they are relative
addresses. For instance, if the formula, Al+B2, is copied to the
cell below, then it would automatically be changed to A2+B3.
Although somewhat tedious in appearance at first glance, the idea
of copying formulas is extremely easy to do and allows the calcu
lations in any cell to be checked directly because the formula is
12
W~!3
stored within the cell. This is a major advantage over tradi
tional computer programming methods wherein a direct check of
calculations is relatively difficult. This technique also allows
the user to compare the results of several different distance
weighting formulas very quickly. Besides distanceweighting
spatial distribution, the user may select many other techniques,
e.g. those of a polynomial fit, a nearest neighbor distribution,
or a weighted or unweighted average of nearby gages. Simple
statistics can be applied to each data set in order to determine
the best distribution.
Parameter Estimation
Much of the early modeling work in the Cypress Creek study
consisted of parameter estimation, most of which was accomplished
manually. As experience was gained in the use of spreadsheets,
several methods were developed to use Lotus 123 for parameter
estimation. All of the support data and preliminary analysis for
the parameter estimation were put onto the spreadsheet. This
step allowed us to automate the bulk of the modeling effort which
deals with parameter estimation and data entry. The spreadsheet
files include complete documentation of all assumptions and cal
culations, thereby providing a significant savings in time and
much improved quality control on the modeling process. In
general, the spreadsheet can be used as a preand postprocessor
for these larger models. Such an application to the EPA SWMM
model has been made (Miles et al., 1986).
13
S7/
Water Budgets
A spreadsheetbased annual water budget for the Cypress
Creek study area is found in Table 3. Since precipitation,
evapotranspiration, runoff, and well elevations are known, it is
possible to calculate the change of storage (difference in con
secutive well elevations) and the residual. The residual is
calculated by the water budget equation:
RL= PETRDS ................................... (4)
where RL = residual (in.), P = precipitation (in.), ET = evapo
transpiration (in.), R = runoff (in.), and, DS = change of
storage (in.). This table was used to estimate the leakance in
the area, which is represented by the residual.
Statistical Analysis
Lotus 123 includes a variety of functions for performing
simple statistical analysis, e.g., mean, minimum, maximum, and
standard deviation. The current version, Release 2, also
includes regression analysis. The spreadsheet provides a very
convenient pre and postprocessor for PC or mainframe
statistical packages due to its data entry and graphics capa
bilities. Also, it is easy to program simple statistical calcu
lations directly in 123, e.g., the five point moving average
values shown in Table 3.
SPREADSHEET MODELING
Although comprehensive hydrologic computer models provide a
valuable service when dealing with larger problems, it is often
advantageous to use smaller models to better understand the
14
5./ 5
