Making It Fit by Leaf and Murphy

Part IV – Help from Excel

Dr. Latimer knew that in order to determine the proper parameters it was not adequate to use arbitrarily selected values. Computer programs, such as Excel, are used to determine these. He entered the data into a spreadsheet called Age_Length.xls.

Following the same methods you used in Part III, there are a number of columns that need to be added to the spreadsheet in order to determine the sum of the (observed − expected)2. What are the contents in these columns? Hint: If you follow the methods in Part III, you will need to insert formulas into three columns and add the numbers of the last column to get a single value (the sum of the squared residuals).

Questions

  1. Plot the observed data: length and age. Using Excel: Insert → Chart → XY (Scatter) → Next → Series Tab → select X (Annuli count) and Y (Length).

  2. As we previously did, we will use the equation of the von Bertalanffy growth function to determine expected values. This time, you will program Excel to do the work for you. Use the parameters k and L∞ from cell B3 and B4 to calculate the values in column C.

    You will use the absolute reference feature in Excel to do this:

    Figure 8

  3. Plot the expected data (expected length). How do you think that the fit of the curve can be improved?

  4. Change the values of k and L∞ in the cells B3 and B4. Describe how your estimate of the expected lengths changes as the parameters are altered. For example, change the values of k to smaller values. Observe the fit of the corresponding curve. How does this compare to the first graph?

Go to Part V – Help from Solver

Originally published at http://www.sciencecases.org/reef_fish/case4.asp

Copyright © 1999–2012 by the National Center for Case Study Teaching in Science.  Please see our usage guidelines, which outline our policy concerning permissible reproduction of this work.

Home