Link to USGS home page.
Scientific Investigations Report 2006–5024

Documentation of a Spreadsheet for Time-Series Analysis and Drawdown Estimation

By Keith J. Halford

Version 1.2


thumbnail of report's cover page

Drawdowns during aquifer tests can be obscured by barometric pressure changes, earth tides, regional pumping, and recharge events in the water-level record. These stresses can create water-level fluctuations that should be removed from observed water levels prior to estimating drawdowns. Simple models have been developed for estimating unpumped water levels during aquifer tests that are referred to as synthetic water levels. These models sum multiple time series such as barometric pressure, tidal potential, and background water levels to simulate non-pumping water levels. The amplitude and phase of each time series are adjusted so that synthetic water levels match measured water levels during periods unaffected by an aquifer test. Differences between synthetic and measured water levels are minimized with a sum-of-squares objective function. Root-mean-square errors during fitting and prediction periods were compared multiple times at four geographically diverse sites. Prediction error equaled fitting error when fitting periods were greater than or equal to four times prediction periods.

The proposed drawdown estimation approach has been implemented in a spreadsheet application. Measured time series are independent so that collection frequencies can differ and sampling times can be asynchronous. Time series can be viewed selectively and magnified easily. Fitting and prediction periods can be defined graphically or entered directly. Synthetic water levels for each observation well are created with earth tides, measured time series, moving averages of time series, and differences between measured and moving averages of time series. Selected series and fitting parameters for synthetic water levels are stored and drawdowns are estimated for prediction periods. Drawdowns can be viewed independently and adjusted visually if an anomaly skews initial drawdowns away from 0. The number of observations in a drawdown time series can be reduced by averaging across user-defined periods. Raw or reduced drawdown estimates can be copied from the spreadsheet application or written to tab-delimited ASCII files.

This report is contained in the following files:

For viewing and printing upon download. (This version of the report is accessible as defined in Section 508 of the Rehabilitation Act of 1973, Amendments of 1998.)

Template and spreadsheets for download.




   Purpose and Scope

Water-Level Components
   Barometric Effects
   Tidal Effects
   Background Water Levels
   Moving Averages and Differences

Drawdown Estimation with Synthetic Water Levels

Nevada Example

Drawdown Detection Limits

Instructions for Time-Series Analysis Workbook
      Cell Formatting in the Time-Series Analysis Workbook
      Step-by-Step Instructions
      Pasting Data into the Time-Series Analysis Workbook
      Initializing and Filtering Time Series
   SHOW Page
      Viewing Time Series
      Magnifying Selected Periods of Time Series
      Graphically Defining Fitting, Estimation, and Feel-Good Periods
    DETREND Page
      Major Features of the DETREND Page
      Loading and Fitting Synthetic Series
      Viewing Components and Estimating Drawdowns
      Viewing, Filtering, and Exporting Drawdowns


References Cited


Adobe Reader To view files in PDF format, download free copy of Adobe Reader.

*Downloading Suggestion: It is best to download and save a large PDF file to your hard drive rather than to open it inside your browser.

   A standard click may automatically open the PDF file inside the browser but doing so will result in a very slow load.
   Downloading the PDF file may take several moments but will be worth the wait.
   Once it is downloaded, open the PDF from your hard drive using Adobe Acrobat—it will open in a fraction of the time it would take to open the PDF over the Internet.

Assistance for PC & Mac Operating Systems

Microsoft Windows and Explorer or Netscape:

1. Right-click the link to a file, and then choose 'Save (Link) Target As' from the pop-up menu.
2. In the Save As dialog box, select a location on your hard drive, and then click Save.

Apple OS and Firefox, Explorer or Safari:

1. Right-click (or Control-click) the link to a file, and then choose 'Download Link to Disk' from the pop-up menu.
2. In the Save dialog box, select a location on your hard drive, and then click Save.

Send questions or comments about this report to the author, Keith Halford at, 775.887.7614.
For more information about USGS activities in Nevada, visit the Nevada Water Science Center home page.

U.S. Department of the Interior, U.S. Geological Survey
Persistent URL:
Page Contact Information: GS Pubs Web Contact
Last modified: Thursday, March 07 2013, 07:34:56 PM
FirstGov button  Take Pride in America button