Accepted for publication,J. of Water Resources Planning & Management,ASCE
WATER RESOURCES ANALYSIS
(fi USING ELECTRONIC SPREADSHEETS
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
-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,
HYDROLOGIC ANALYSIS AND THE ELECTRONIC SPREADSHEET
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.
SPREADSHEETS AND DATA ANALYSIS
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,