Tips for Data Manipulation: Tutorial


Information to get started:
  • The lesson below contains step-by-step instructions and "snapshots" of what each step looks like when carried out in a Microsoft Excel workbook. Blue shading of information in the Excel illustrations denotes changes made from the previous step. Dots placed in three consecutive rows indicate that a portion of data is hidden from sight.
  • You can download an Excel workbook containing the complete data set by clicking on the "Download Data" link below. It contains each calculation step on a separate worksheet. To move between steps, click on the tabs at the bottom of the excel window.
  • When you download the file, it may open in your browser window. You may wish to use the "save as" function to save the file to a local drive and then reopen it in Excel. This will make it easier to flip between the online lesson and the example workbook.
  • Finally, we want to remind you that the techniques explained on this site are statistically based; therefore results must be viewed as predictions and not as facts. Please use the techniques and the information obtained from them responsibly!

Download Data

View and print this webpage as a pdf file.


Step 1: Copy Daily Streamflow Data from USGS web site into Excel Spreadsheet

  • Go to http://oregon.usgs.gov
  • On the left sidebar, under Historical Data, select Streamflow
  • Select Daily Data
  • Check box under Site Identifier for Site Name and Submit
  • Type in Alsea under Site Name and select match any part and Submit
  • Select gage near TIDEWATER (14306500)
  • In the Retrieve data from boxes enter the date range of "1990-10-01" to "2000-10-01"
  • Select Tab-separated data and Display in browser and Submit
  • Select the entire data set to copy
  • Paste Special as text (this will separate the data into columns)

Step 2: Organize spreadsheet with data

  • Eliminate extraneous data (i.e., column E)
  • Add titles to remaining Four columns

NOTE: Data are listed in water years, hence 10/1/1990– 9/30/1990 is Water Year 1990.

  • Label Sheet

Step 3: Obtain Monthly Averages

MANUALLY

  • In fifth column, use the average function in Excel to obtain the average for each month for the first four years.

Copy the Monthly Average Column for the first four years and paste to rest of data set. This will compute the monthly averages for the remaining years.

  • Organize Monthly Data by Year


IF USING A USGS GAGE, MONTHLY STATISTICS ARE ALREADY CALCULATED

  • Go to http://oregon.usgs.gov
  • Select Historical Water Data
  • Select Surface Water
  • Select Statistics (Monthly)
  • Check box under Site Identifier for Site Name and Submit
  • Type in Alsea under Site Name and select match any part and Submit
  • In the Retrieve data from boxes enter the date range of "1990-10-01" to "2000-10-01"
  • Select Tab-separated data and Display in browser and Submit
  • Select gage at TIDEWATER (14306500)

** Data included in this table are for all of the Alsea gages, be absolutely certain that you are using the data for your desired gage (in this case 14306500)

  • Select Data Set for desired gage to copy
  • Paste Special as text (this will separate the data into columns)

Step 4: Obtain Annual Averages

MANUALLY (Annual Averages are done based on Water Years 10/1/XX-9/30/XX)

  • In Fifth Column, use the average function in Excel to obtain the average for each water year for the first four water years in the period of record.


  • Copy the Annual Average Column for the first four years and paste to rest of Data Set. This will compute the annual averages for the remaining years.

  • Organize Annual Data by Year for Period of Record

IF USING A USGS GAGE, ANNUAL STATISTICS ARE ALREADY CALCULATED

(Annual averages are based on calendar year 1/1/XX – 12/31/XX)

The monthly values can be used to generate mean annual flows by water year.

  • Go to http://oregon.usgs.gov
  • Select Historical Water Data
  • Select Surface Water
  • Select Statistics (Annual)
  • Check box under Site Identifier for Site Name and Submit
  • Type in Alsea under Site Name and select match any part and Submit
  • In the Retrieve data from boxes enter the date range of "1990-10-01" to "2000-10-01"
  • Select Tab-separated data and Display in browser and Submit
  • Select gage at TIDEWATER (14306500)

** Data included in this table are for all of the Alsea gages, be absolutely certain that you are using the data for your desired gage (in this case 14306500)

  • Select data set for desired gage to copy
    Paste Special as text (this will separate the data into columns)

Home | Navigation Tips | Preliminary Estimations | Data Manipulation | Analysis Techniques
Example Applications | Hydro Data Links | Related Links