Using the Rapid Bio-Assessment Data Workbook

This Excel workbook contains fish density and spatial distribution data for all of the streams in the Nestucca, Neskowin and Sandlake basins that were surveyed in the Rapid Bio-Assessment Project during the years 2002, 2003 and 2004. The data were collected by Bio-Surveys under contract to the Nestucca / Neskowin Watersheds Council.

The Excel file “2002-4 Nestucca-Neskowin RBA Workbook v1.xls” has been sent to you as a management and research resource. If you wish, you may copy the data to your own data management system for integrated analysis with other data. Note that the Excel workbook does not contain surveyor field notes, which provide information about tributary junctions, culvert crossings, anadromous barriers, water temperature, etc. This information is available in Access data files by contacting the Nestucca-Neskowin Watershed Council.

Additional copies of the workbook can be obtained by contacting Steve Trask at or at 541-487-4338.

We recommend that you make a copy of this workbook, rather than working in the original sent to you because Excel workbooks or their data can be corrupted after extensive use. If you received the workbook on a CD diskette, the file will be stored as Read Only, and there is no danger of corruption. However, in some cases working off the CD diskette may be tedious due to slow CD ROM access speeds. Open the file from the CD and then save it to your hard drive to both speed up operations and remove the Read Only restriction.

For users having little experience with Excel, basic techniques for using this workbook are presented below. Experienced Excel users might see something useful as well.

DESCRIPTION OF DATA

The basic data are snorkel counts conducted by surveyors between June3 and Sept 7. The counts have been converted into density estimates using pool dimension data.

Location along the surveyed stream is represented as the distance upstream from the stream mouth. These distances were estimated by pacing, and then adjusted according to correction coefficients based on a subsample of tape measurements. A correction coefficient was developed for each surveyor on each stream.

The distribution data observed in each chart represents the abundance of each species in every 5th pool. The trends in abundance are very descriptive of spawning locations and upstream juvenile migrations, as well as upper limits of distribution. Note that only the upper end of Coho distribution is represented within this database. Surveys were terminated two sampled poolsabove the last sighting of Coho juveniles.

Survey location is defined according to:

  • Basin: The watershed containing the surveyed stream.
  • Trib To: The stream into which the surveyed stream flows.
  • Stream: The surveyed stream.

The count and density data are presented in two tabular forms, one designed for ease of data viewing and the other for ease of chart production.

VIEWING THE DATA DIRECTLY

Select the tab called “View Data”. In this presentation of the data, each species has a separate column. This allows you to select (filter for) subsets of the data in order to view species densities according to year and stream.

Use the AutoFilter tool to make your selection by clicking on the buttons in the header row. Each time you select an item from one of these buttons, Excel hides more data from view, restricting the data seen to smaller and smaller subsets. This selection method conforms to the Boolean “and” query method.

Actions that you may find useful:

1)Hold down the Alt key, and sequentially hit D F S to remove all filters and display all data.

2)Hold down the Alt key, and sequentially hit D F F to turn the AutoFilter ON or OFF.

3)To sort the visible data according to Stream, Density, or some other criterion, select a title header cell (e.g., YEAR, STREAM, DENSITY) and then the click on the Sort icon (shows “AZ” and a down arrow). Avoid selecting a data cell. Excel understands the dimensions of the table, and will sort all records in the table according to the chosen field. However, it is important to select only a single header cell when sorting; otherwise Excel might sort only the selected cells as opposed to the whole table. In no case should you select a whole column or some group of columns for sorting, since Excel will then sort the selected columns independent of the other columns, making the table useless.

4)Control-Home takes you to the first data cell (A2).

5)Control-Z reverses the previous action to undo a mistake (may be repeated to back out of sequence of changes).

6)To hide columns: Highlight whole columns by clicking on column labels (A, B, etc.). Use the Window menu to Hide the column(s) and thus make your view of the data more compact. Later, highlight the columns to the left and right of the hidden columns, and use the Window menu to Unhide.

When you activate AutoFilter or use the Sort button, Excel automatically determines the extent of the table, and assumes that the top row is the header, and all cells below are data cells. However, Excel cannot make these judgments properly if the cell currently selected is empty or if the adjacent cells are empty. Therefore, you should always select a header cell before enabling AutoFilter or using the Sort function. Otherwise, you might easily scramble the table.

You will notice that the header row (Row 1) does not scroll. This was accomplished by selecting Row 2 (not Row 1) and then choosing Freeze Panes from the Window menu.

THE CHART DATA TABLE

The Chart Data table is composed differently from the View Data table. In the Chart Data table, the individual fields for each species are removed in favor of a single field named “Species”. This organization of the data allows more effective data selection when building charts, but is not convenient for directly viewing the data. Attempts to Autofilter or Sort the Chart Data table may result in long waits because this table contains a large number of records. Its best just to ignore this table, unless you want to export it to MS Access or another database manager.

USING THE PIVOTTABLES TO UPDATE CHARTS

Excel uses PivotTables to select and reduce data to a suitable size for chart building. A PivotTable worksheet and several preformatted charts have been set up. You can use the PivotTables provided to have the chart show the stream, species, and year of interest.

Select the PivotTable tab. Two PivotTables are presented, one for updating the three density charts and the other for updating the fish count chart. (Multiple charts are used for the density data to allow proper scaling of the Y-axis according to species-specific full seeding level--see below).

1)Select either the Density PivotTable or the Count PivotTable.

2)Select the stream.

3) Select the year.

4)Select the species.

5)Let the PivotTable update, and then view the appropriate chart.

If you update the Density PivotTable, then you will view either "Coho Chart", "Cut-Sthd-Chin Chart", or "0+ Chart" according to the species selected. If you update the Count PivotTable, then you will view only "Count Chart (all species)".

It is good idea to make sure the updated chart shows the correct range of data developed by the PivotTable. Occasionally, Excel will not update its chart correctly and might include only a portion of the newly selected PivotTable data. This is most likely to happen if a new data selection makes the PivotTable larger than produced in a previous selection. If the chart data does not match the PivotTable data range, you will want to manually select the complete data range:

  • Right-click the chart (not a data point) and select Source Data.
  • This will cause Excel to highlight the portion of the PivotTable that it has used to build the chart.
  • Scroll down to find the bottom-right cell of the pivot table.
  • Left-click once in this cell.
  • Click OK.

SCALING THE X-AXIS OF THE CHART

The chart tab presents the data in a column chart format, because this format is easy on the eye. However, column charts are like most other Excel charts in not allowing the X-axis to be scaled. (The X-axis shows “Total Distance from the Stream Mouth”.)

The X-axis of a column chart is instead presented as a series of labels, in the same way “North, South, East, West” would be presented. This means that in a column chart all distance values are equidistant from each other on the X-axis of the chart. If a chart lists four locations along the stream at 0, 10, 20, and 1000 feet, then the distance on the chart between 10 and 20 feet will be the same as between 30 and 1000 feet, because the values are not mathematically scaled.

If you wish the chart to show the true scale along the X-axis, right-click anywhere on the chart, choose “Chart Type”, and change it to an XY Scatter.

SCALING THE Y-AXIS OF THE CHART

The Y-axis shows fish density (Fish/Sq Meter). A proper presentation of the chart requires that an appropriate maximum value be set for this axis, based on the full seeding density for each species. The maximum density is well studied for Coho, but is not firmly established for the other species. In the workbook, three charts have been set up with different Y-axis scalings.

Coho: Current research sets the full summer seeding level at 1.5 fish/sq meter, although this value may be slightly conservative. This density is used as the maximum value in the first chart of the workbook.

Cutthroat, Steelhead and Chinook: Densities of each of these species may exceed 0.75 fish/sq meter, but this would be an uncommon event. This value is used to scale the second chart in the workbook but does not represent an estimate of full seeding due to the lack of appropriate research.

0+ fish: Densities of 0+ age salmonids may exceed 3.0 fish/sq meter, but this would be an uncommon event. This value is used to scale the third chart in the workbook. This represents a relatively uncommon but attainable level of seeding in coastal basins.

To alter a chart from these values, you may right-click the Y-Axis, select the Scale tab, and reset the Maximum value.

DISTINGUISHING BETWEEN “NO DATA” AND ZERO DENSITY

An empty PivotTable should be interpreted as “the stream was not surveyed that year, and nothing can be said about the presence or absence of any species for that year”. An empty PivotTable should not be interpreted as zero densities.

On the other hand, if the PivotTable generates a series of zero densities, these are real values and can be interpreted as “the stream was surveyed in the selected year, but the species was not found”.

SOLVING PROBLEMS

Excel is a spreadsheet, not a database manager, and generates a lot of overhead when managing large tables like those in this workbook. The workbook file is large, and the potential for format or data corruption exists. You might want to avoid saving your work to prevent saving data errors that creep in.

If Autofiltering or other activities slow down, then the content of either the system memory or the workbook file itself may be corrupted. You can try restarting the system to see if it is a memory problem. If this does not help, you should make another copy of the original file sent to you.

If Excel produces Fatal Errors or Protection Faults when opening the workbook, your system may not have sufficient available memory. Try running Windows with no startups and open only the workbook.

1