Title: Water Resources Analysis Using Electronic Spreadsheets
Full Citation
Permanent Link: http://ufdc.ufl.edu/WL00000690/00001
 Material Information
Title: Water Resources Analysis Using Electronic Spreadsheets
Physical Description: Book
Language: English
Spatial Coverage: North America -- United States of America -- Florida
Abstract: Water Resources Analysis Using Electronic Spreadsheets, by Michael C. Hancock and James P. Heaney
General Note: Box 7, Folder 1 ( Vail Conference 1987 - 1987 ), Item 83
Funding: Digitized by the Legal Technology Institute in the Levin College of Law at the University of Florida.
 Record Information
Bibliographic ID: WL00000690
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

Accepted for publication,J. of Water Resources Planning & Management,ASCE



Michael C. Hancock 1 and James P. Heaney 2, M. ASCE


Comprehensive mathematical models have become very popular

Stools for solving water resources problems. In a recently

completed study, two popular hydrologic models were used: the

Hydrologic Simulation Program-Fortran (HSPF) for simulating sur-

face hydrology, and the USGS model for simulating groundwater.

The HSPF model required 150 parameter estimates and the USGS

model required 4000 parameter estimates. Initially, the

parameter estimation was done manually. Then, it was decided to

use an electronic spreadsheet available on microcomputers in

order to expedite the parameter estimation process and to provide

better quality control on how these values were selected.

As experience was gained with using spreadsheets, it became

apparent that they are very powerful computational tools. Thus,

a variety of analyses were done that traditionally required

mainframe computers. Some of these applications are described in

this paper.

-Graduate Assistant, Department of Environmental Engineering
Sciences, University of Florida, Gainesville, FL. 32611.
professor of Environmental Engineering Sciences, and Director,
L'lorida Water Resources Center, Univ. of Florida, Gainesville,
F'L. 32611.



Lotus 1-2-3, the most popular of the electronic spread-

sheets, is so named because three programs are combined into one:

the electronic spreadsheet, business graphics, and data manage-

ment programs. Version 2 of this spreadsheet is a matrix of 254

rows and 8192 columns, and can be considered to be a very large

sheet of electronic paper (Lotus Development Corporation, 1985).

Over 12000 page equivalents can be placed onto one spreadsheet.

The user is able to add or erase calculations, text, or data in

any block, or cell, at any time, and can import or export any

part of the work and data to and from other software. The data

handling capabilities allow the user such an easy method of

manipulation that many types of problems can be solved with

little effort. It literally replaces the paper and pencil.

Besides the comprehensive manual that accompanies this soft-

ware, several books have been written on the uses of Lotus 1-2-3

(e.g., LeBlond and Cobb, 1983, Anderson and Cobb, 1984, Ridington

and Williams, 1985), and a monthly publication, LOTUS, is

dedicated to advancing the use of the program. Although these

many guides exist, Lotus 1-2-3 is almost self explanatory once a

few basic concepts are understood.

The electronic spreadsheet has many uses in engineering

because of its versatility in data handling and analysis. In

water resources, this analytical tool can be very useful in many

areas, including data preparation and analysis, parameter estima-
tion, and simple simulations. Also, it can be used as a pre- and



post-processor for larger models. The next section introduces

spreadsheets and data handling, with emphasis on the knowledge

base capabilities of the spreadsheet. An example is used to

illustrate how this simple, yet effective, method can be used in

hydrologic problem solving. Then, these ideas are expanded to

include data and parameter analysis for use in comprehensive

hydrologic models. Also, methods of performing basic hydrologic

analyses on the spreadsheet are presented that may preclude the

need for more comprehensive models. Examples are taken from a

recently completed water resources study of the impact of well-

field development and surface drainage on the hydrology of the

Cypress Creek Basin north of Tampa (Heaney et al., 1986). A more

complete explanation of the use of spreadsheets in hydrological

analysis can be found in Hancock (1986). Johnson (1986) des-

cribes the use of spreadsheets as part of a decision support

system for water resources. Olsthoorn (1985) shows how to use

spreadsheets for groundwater modeling.



Main-frame and microcomputer based geographical information

systems are available, but they require specialized expertise and

facilities to use. Fortunately, a simple mapping system using

the spreadsheet can be devised.

A spreadsheet-based map of the Cypress Creek study area

showing the location of each raingage is presented in Figure 1.

f Through the use of a 1-2-3 XY plot and data labeling commands,

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