Title: Water Resources Analysis Using Electronic Spreadsheets, Table 2: Lotus Model, Distance Weighing Distribution for Rainfall at Cypress Creek
CITATION THUMBNAILS PAGE IMAGE ZOOMABLE
Full Citation
STANDARD VIEW MARC VIEW
Permanent Link: http://ufdc.ufl.edu/WL00000695/00001
 Material Information
Title: Water Resources Analysis Using Electronic Spreadsheets, Table 2: Lotus Model, Distance Weighing Distribution for Rainfall at Cypress Creek
Physical Description: Photograph
Language: English
 Subjects
Spatial Coverage: North America -- United States of America -- Florida
 Notes
Abstract: Water Resources Analysis Using Electronic Spreadsheets, Table 2: Lotus Model, Distance Weighing Distribution for Rainfall at Cypress Creek
General Note: Box 7, Folder 1 ( Vail Conference 1987 - 1987 ), Item 88
Funding: Digitized by the Legal Technology Institute in the Levin College of Law at the University of Florida.
 Record Information
Bibliographic ID: WL00000695
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 2. Lotus Model, Distance-weighting
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$78-A10)2) + (($B$78-B10)2))1/2 .............(2)

or the equivalent of

((X1-X2)2 + (Yl-Y2)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 distance-weighting

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 1-2-3 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 pre-and post-processor

for these larger models. Such an application to the EPA SWMM

model has been made (Miles et al., 1986).




13

S7/










Water Budgets

A spreadsheet-based 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= P-ET-R-DS ................................... (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 1-2-3 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 post-processor 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 1-2-3, 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-




University of Florida Home Page
© 2004 - 2010 University of Florida George A. Smathers Libraries.
All rights reserved.

Acceptable Use, Copyright, and Disclaimer Statement
Last updated October 10, 2010 - - mvs