NAU Biology BIO 326
NAU
Syllabus The Class Communicate Library Instructor
HelpGet Started
BIO326 : Individual : Adaptation to Environment : Complex Changes

Exercise: Complex Changes

To complete this assignment successfully, you should:

  1. Study the assignment carefully
  2. Enter your response(s) in the space(s) provided
  3. Fill in your Name and Email address
  4. Send the Assignment

DUE: 4-Feb-2000. Work sent after this date will receive a grade of zero.
This week you will learn to extend your facility with spreadsheets to more complex tables and equations, including graphing results. After completing this exercise, you should have developed the skills and confidence to work through any other arithmetic and math questions in this course.

1. One of the primary tools used by field onithologists to study birds is to band the birds and follow them. American robins were so studied, and the following numbers of survivors were found based on an original 1000 individuals.

Age

Survivors
0-11000
1-2497
2-3229
3-499
4-536
5-610
6-76

Open a spreadsheet, e.g., Excel, and enter the information from the above table (including the headings) in columns A - B. (Note: To prevent Excel from doing a subtraction on the ages, click with the right mouse button on the A above column A to select the entire column. Move the cursor into the selected column and right click, click on Format Cells. In the Format Cells dialog box click the number tab, click Text, click OK. This will make Excel treat data in column A as text and not numbers.)

Select the entire table by holding down the left mouse button and dragging the cursor from the top left to the bottom right (cell A1 to cell B8) and release the mouse button. On the menu bar, click on Insert, Chart (or click on the chart icon on the tool bar). From the Chart type window, pick the Line chart and then click Next. Click Next, Next, & Finish. You now have a graph of survivors plotted against age of the individuals. It is clear that lots of birds die when they are young, but fewer birds die as they get older.

In column C, put the heading, "Log e." Click on cell C2. Then click on fx on the tool bar. In the "Function category" window, click on "Math & Trig." In the "Function name" window, doubleclick on "LN." Click anywhere in the gray area of the function dialog box and drag it down and to the right so that you can again see the columns containing your data. Click on the number of survivors in the adjacent cell (cell B2). Click OK in the function box.

Put the cursor on the selected cell C2 and drag it down to the bottom of the table (cell C8) to select that column. Hold down the CTRL key and type "d" to repeat the formula down the column. You have now calculated the logarithms of survivors at each category.

Select the data in column A (place the cursor on cell A1 and drag it down the column. Holding down the CTRL key, also select the data in column C. With the data in columns A and C selected, make a chart in the same way you did above.

The graph plotting log survivorship against age shows the RATE of mortality as age increases. A straight line diagonal has been commonly found in birds, and implies that the probability of death is the same regardless of age.

2. Continuing to column D, place "Log 10" in cell D1. Then calculate logs to the base 10 using the Math and Trig function LOG10. As you did with loge survivorship, plot log10 survivorship against age. Place the cursor in the white area of the graph (it is selected since you can the black "handles" around its edges) and drag it down so you can see the previous graph too. Select the entire table and paste it into the text box below. What differences and similarities do you see between the two logarithmic graphs?

3. In the textbook on page 336 is the well-known life table of survivorship for the dall mountain sheep. Using a spreadsheet, enter data from this table for age and number surviving (except for age 14-15). Multiply number surviving by 1000 before entering into the spreadsheet, i.e., enter 1000 instead of 1.000. As above, calculate logs to the base 10 and plot the survivorship curve.

Paste the entire table into the textbox below. Note that there is high mortality in the first year of life, followed by very low mortality. Towards the end of life, mortality increases as the individuals get older (= senescence). This pattern is characteristic of vertebrates, including humans.

4. A population of mice was studied. Survival was measured as a probability and the average number of offspring produced by the mice at each age was determined. The number of mice at each age were counted.

Age
  Survival
  Fecundity
  Individuals
00.5020
10.8110
20.5340
30230

Using a spreadsheet, enter these data. In cell E1 enter the number "1," and in cell F1 enter the number "2." To calculate the number of mice in the population at each age at the next time interval, figure out how many survive from the previous time intervals and how many offspring they have. For instance, the 20 newborn mice have a probability of 0.5 of surviving to age 1. In cell E3, type the equals sign. Click on cell D2. Type an asterix. Click on cell B2. Type the Enter key. You should have 10 mice, aged 1 which survived from the newborns in the previous time period.

You can use this same formula to calculate the number of mice which survive from age 1 to age 2. However, as you move the formula over to succeeding columns, you would like the cell references to the number of individuals to move but the cell references to the survival probabilities to remain in column B. You need to make the reference to column B in the formula an absolute reference. Click on cell E3. The Formula Bar just above the spreadsheet shows the formula for this cell, "=D2*B2". Move the cursor in front of the "B" in the formula and type a "$". Then type the Enter key.

Click again on cell E3 and repeat the formula down the column for the rest of the age groups. You should find 10, 8, & 20 mice.

To calculate the number of newborns from these mice, you need to add the offspring from all the age groups together. Click on cell E2, and type the equals sign. Click on E3, type an "*", and click on C3. Type a "+", click on E4, type an "*", and click on C4. Type a "+", click on E5, type an "*", and click on C5. Type the Enter key. You should see 74 young mice.

As with the formula for survivors, you need to make the references to fecundity in column C absolute references. Click on cell E2. In the Formula Bar, type a "$" in front of each "C". Type the Enter key. You should still see 74 newborn mice.

To repeat the formula in subsequent columns to the right, select the numbers of mice in column E (click on cell E2 and drag), then move the cursor to column F5. Holding down the CTRL key, type an "r" (to repeat the formula to the right). You should see 69, 37, 8, 4.

For each of the three time intervals, sum all the mice to find the total population size. In the columns for time intervals 1 and 2 under these sums, calculate the ratio of the population sizes at that interval with the size of the previous interval (i.e., calculate the factor by which the population has increased from one time interval to the next). Paste this entire table into the textbox below.

5. If the probability of survival from age 1 to age 2 were 0.6 instead of 0.8, what would be the total population size at time interval 2? If, in addition, one-year-old mice had a fecundity of 2 instead of 1, what would be the total population size at time interval 2?

6. Populations with no resource restrictions grow at a rate which is proportional to the size of the population, according to the formula Nt = No * ert, where N is the population size at time t, No is the initial population size, and r is the rate of increase. To calculate the size of a population of pheasants starting at 8 individuals and growing at a rate of 1.02 individuals/individual/year for 5 years, enter the following data in a spreadsheet.

Inc Rate1.02
Year
  Individuals
08
1 
2 
3 
4 
5 

To calculate the number of individuals in the population at the next time interval, click on cell B4 and type the Equals sign. Click on cell B3 and type an "*". Get the Math and Trig function EXP (for exponeniation, which is the inverse of taking a logarithm to the base e) and doubleclick on it. Move it to the side so you can see the data. Click on the cell containing the increase rate (B1), type an "*", and click on the cell containing the time 1 (A4), and type the Enter key.

Before you repeat this formula for the other time intervals, you will need to make the references to the cell containing the increase rate and the cell containing the initial population size absolute. Move the cursor to the Formula Bar and put a "$" in front of the 1 in B1 and in front of the 3 in B3. Type the Enter key. Repeat the formula down the column. Paste the table into the text box below.

7. If you started with an initial population of 100 instead of 8, what would be the population size at year 5? If, in addition, the growth rate were 1.5 instead of 1.02, what would be the population size at year 5? If you censused the population at year 5 and found 60,000 pheasants, what could you conclude had changed for the population during the five years?


For grading purposes, please provide the following information:

Your Name:  
Your Email address:  

Once you have filled in all of the areas above, click the Send the assignment button below to send the assignment to the instructor.

  

E-mail Professor Gaud at gaud@jan.ucc.nau.edu
or call (520) 523-7516
NAU Copyright 1999 Northern Arizona University
ALL RIGHTS RESERVED