More Detective Work

Let’s work through an example together using Excel, test a related hypothesis, and look at methods we can use to test the hypothesis. Open up this spreadsheet:


This spreadsheet is like the one you have for your watershed but we have removed some columns. We have also filled in some values that were missing to make it easier to see patterns. In your data, there will be some missing values because all possible tests were not done every time.

Here are some general points about the data:

  1. The leftmost column shows a place called HUC_14 which represents a code that represents one part of the watershed. Later we will look at maps of your watershed that show where those places are.
  2. The next column shows a year when the samples were taken. Notice that each place was sample in different years and some places were sampled in more years than others.
  3. There are several sets of variables: biological and habitat measures (IBI, ICI, QHEI, Substrate); chemical concentrations, minimum, maximum, and average for many different chemicals; riparian land use.
  4. Notice the rightmost column (AR) labeled as HUCdate. Here we have an index composed of the 5 numbers on the right of the HUC and the two numbers on the right of the date. Click on a value to see the formula that created this value. It allows us to label our graphs to identify both the place and time of each value.

Let’s make our first scatterplot. To make it easier we will copy selected columns to the bottom of the spreadsheet. This was already started for you. HUCdate was copied to cell E60 for all of the values. Now go up from there and copy the Av-IBI column so it is to the right of the HUCdate. Do the same with Av_QHEI and Minimum DO Probe. You now have four columns at the bottom of the page.

Make an X-Y scatter by:

  1. Highlighting the Av_IBI and AV-QHEI numbers
  2. Click on the graphing icon at the top of the page
  3. Choose X-Y scatter as the graph type
  4. Click next if the graph looks ok
  5. Fill in a title and axis labels: Avg. QHEI vs. Avg. IBI; IBI, QHEI
  6. Choose to put the graph on a new sheet called qheiibi

Look at the graph. Do you think there is a good correlation? Why?