Instructions for Data Submission 3: Lemna Population Growth
Download the third data submission template and open it in Excel. Before you do anything else resave the file with the following name, ALL IN LOWER CASE: your 3 digit lab section number + your last name + the first 3 letters of your first name + the last 4digits of your student#.xls. Example: 101collinsnic1234.xls. If you don’t submit the file so named, your file will stand a good chance of getting lost or corrupted.
To facilitate some of the plots and calculations in this submission, everyone in your lab group should submit the counts for day zero. On the following lines you should submit one line of data for each day you personally recorded data on the Lemna populations for your lab group. If, later in the data collection schedule, two people from your group separately collected data on the same day, each of the two people should submit their own counts for that day. (If two members of your group made counts together on the same day, so that only one set of counts was obtained, only one of you should submit data for that day. Flip a coin to decide which one submits). Each line you submit will include information to identify you and your group, plus counts of the number of individuals in each of your group's vials and, on the dates when leaf size data were recorded, counts of the numbers of individuals in each size class for each vial. In addition, before you submit the file to the data manager you will:
1. Instruct Excel to calculate for each data line the number of days since the experiment began.

2. Instruct Excel to calculate for each day the average number of individuals per vial for each of your light levels,

3 Only for days where these data were collected, enter the total number of individuals in each size class across all the vials at each of the two light levels. On days when these data were not collected, leave these columns blank,

4. Calculate some basic population growth parameters based on your data.

5. Plot N vs time and log(N) vs time, and superimpose reference lines that describe unlimited or geometric population growth.
Part 1: Submitting the raw data lines
Specifications for what goes in each column of the data template for each data line are below. Many of the columns will have the same entry for every data line you submit. Excel makes it very easy to copy what's in a range of cells in one place, into an equallength range of cells elsewhere. However, one of the most common spreadsheet errors arises when Excel tries to be too helpful and changes parts of your data entry that you don't want changed over a cell range. Be sure to check a cell in each column and row of a range you copy to, to be sure that Excel hasn’t done something more than you expected. To prevent Excel from adjusting cell contents during copy operations you can experiment with holding down the Alt or Ctrl keys while you drag the lower righthand corner of the source cells across the range you want to fill.
Column Title

Format of required entry


TA

Binks, Foster, Rennie, or Krupica


Section Number

Must be a 3 digit number; see the table


Group

The letter designation for your lab group: A,B,C,D,E, F, or U.


Group Size

The number of students in your lab group


Reporter

Your last name followed by the first 3 letters of your first name, with no space, followed by the last four digits of your student number all in lower case. E.g. collinsnic1234


FIVE COLUMNS of information specifying the year, month, and day you set up the experiment and the month and day of the measurement being reported.

Enter numbers in each of these columns. Year should be four digits. Month and day of month should not include leading zeros. (Leading zeros will not be displayed anyway under the "General" format applied to these columns.


Days since the beginning of monitoring (cells highlighted yellow)

Use Excel's DATE function to enter a formula subtracting the starting date from the observation date. The syntax should be: =date(year,month,day)  date(year,month,day), where year, etc. are cell locations for the appropriate values in the columns to the left of the formula. Confirm that your formula gives the correct number of days before you go further. You may find you have to change the order of the month and day specifications to allow your copy of Excel to formulate the date correctly.


LOW LIGHT:# in each vial at low light (2 COLUMNS)

In each of these 2 columns enter the number of individuals you counted in a low light vial on the data collection date specified 2 columns to the left. If you did not call your lowlight vials 1 and 2, then order the vials in alphabetical order. If one of your vials was lost, or if you never had 2 vials, then write an explanatory note in the Comments column and leave the appropriate cells blank.


LOW LIGHT: Average # per vial in low light (cells highlighted yellow)

Enter a formula to calculate the average number of individuals in the 2 lowlight vials. This will require you to use the AVERAGE function and to specify a range of cells. The AVERAGE function ignores cells that are blank, so calculating the AVERAGE value across a range of several cells, one of which is blank or has a label in it, will give the same result as calculating the AVERAGE across a range containing only the numeric cells. N.B: if you leave a space in your formula between the word AVERAGE and the left parenthesis that contains the cell range over which you want to calculate, Excel will not understand that you want to use the AVERAGE function, and will not do the calculations you expect in your formula. Note that if you use relative cell references in your formula, you will only have to enter the formula into the top data cell in this column. If you then copy the formula into the cells below, the cell references will automatically adjust themselves to calculate the averages from the data that are on the same line as the formula. Check to be sure this happens correctly! This automatic adjustment of addresses is one of the most powerful (and dangerous) capabilities of spreadsheets.


# of pads 1 mm wide in both lowlight vials combined

Enter the combined total of the 1mmwide pads from both of your low light vials, for any days that you yourself measured individual widths. For days where you made no width measurements leave these columns blank. If no pads were this wide on a day you measured widths, enter a zero. It is important for you to distinguish between blank cells, which indicate that no data were collected, and zeros, which will be taken as actual measurements or counts.


# of pads 2 mm wide in one or both lowlight vials

See instructions for 1 mm pads


# of pads 3 mm wide in one or both lowlight vials

See instructions for 1 mm pads


# of pads 4 mm wide in one or both lowlight vials

See instructions for 1 mm pads


# of pads 5 mm wide in one or both lowlight vials

See instructions for 1 mm pads

HIGH LIGHT: # in each vial at high light (2 COLUMNS)

See instructions for low light counts


HIGH LIGHT: Average # per vial in high light treatment (cells highlighted yellow)

See instructions for low light average


# of pads 1, 2, 3, 4 and 5 mm wide, summed across one or both high light vials (FIVE COLUMNS)

See instructions for low light width data.


Email

Enter your email address


Comments or Notes

Use this column to write anything you think is important to clarify your submission, especially something about the data on the same line of the Comment entry. Leave it blank if you have nothing that needs saying. The text you type will extend as far as necessary to the right of the column edge after the column fills up. If you enter something into the column to the right of the Comments or Notes column, then the extended text will be hidden, but not destroyed. If you select the cell, the entire written text will show up in the formula bar at the bottom of the command bars above the actual spreadsheet.


Part 2: Graphing data vs models:
Briefly, your objectives now will be to:

use the limited data you are reporting to calculate the finite growth rate, lambda, and an average population growth rate, deltaN/deltat, for one of your Lemna treatments,

use these values in equations to generate a pattern of unlimited population growth and constant population growth at the calculated rates,

plot these model population growth curves and your reported data on conventional and semilog axes.
The calculation tools and graphing procedures you will use will help prepare you for upcoming lab session 4.
Detailed Instructions:
We will do all this work in space directly below your raw data.

Move down three blank lines from your last data line, highlight a worksheet area that includes columns AAB down to row 50 or so and use the cell properties menu to turn off the borders and pattern.

Select the top row of the area being cleared and under its alignment properties click on the "wrap text" box and click on bold in the font properties. The alignment change will allow you to fit lengthy column names within the column widths that have been chosen for the data submission template. Try to avoid changing the column widths of the template. Variation in the widths among submitted files will slow hinder our data manager's efforts to combine all the submitted Lemna data.

Beginning in column A of the headings row you just created, enter the following column headings: Treatment, lambda, delta N/delta t, Time, Lemna, Unlimited, Linear, log(Lemna), log(unlim), log(linear).

You will be using two of your submitted data lines in your calculations of rates. Everyone will use the initial setup condition at time zero. Each member of your lab group will compare those initial conditions to a different subsequent time point. based once again on the alphabetical order of your names, as specified in this link.

Use the population data from your assigned treatments and times to calculate lambda and deltaN/deltat by inserting formulas in the first lines of the appropriate columns. The formulas should refer to the cells containing the average population sizes for your treatment on the appropriate days, and should refer to the appropriate "Days since beginning" entry for all references to the time interval. You will have to be liberal with parentheses in your formulas to be sure that multiterm quantities are used correctly. When in doubt about the order of calculations used by Excel, use parentheses to remove any doubt. To calculate lambda from population sizes that are more than one time unit apart you will need to take the "tth" root of the ratio of population sizes, where "t" is the time elapsed between population counts. The "tth" root of a number is equivalent to calculating the number to the 1/t power. In the next few steps you will use these growth rates that represent growth between the two points in time, to extrapolate how the real Lemna population would grow if it continued to grow in an unlimited or linear way beyond the time period your calculations represent.

Enter a series of time values in the Time column beginning at zero, incrementing by 1, and ending 3 days after the highest value of "Days since beginning" in your data submission lines at the top of the worksheet. If you enter a zero and a 1 in the top two cells, select those two cells, and drag the "square" corner down the column, Excel will automatically increment the numbers for you.

Use formulas to fill the appropriate cells in the Lemna column with the average population sizes for your assigned treatment. When you do not have data for a particular day, leave the cell blank.

Use formulas to repeat in the timezero line in the Unlimited and Linear columns, the timezero value in the Lemna column.

Generate formulas for the second line of the Unlimited and Linear columns that use the lambda and deltaN/deltat values you calculated in step 5 to generate time1 values from the timezero values in the cell above. Figure out the appropriate formula to generate straightline growth using deltaN/deltat. The formulas you want to produce are general ones that calculate population size at time t+1 from the size at time t. That way you will be able to copy the formulas down the columns to the last time point. You will have to use a combination of relative and absolute cell references to ensure that, when you copy these formulas down the columns, the references to the cells just above continue to refer to just one cell above, while the references to the two rates you calculated continue to point to the single cells where these rates reside. Consult Excel help about "cell and range references" for more information about this important aspect of formulas. When you think your formulas are correct, copy them down the columns to the end of the time series, and peek at a few cells to be sure that the cell references have been altered correctly.

On the first line of the the log(Lemna) column enter a formula that takes the log_{10 }of the Lemna value. Then copy this formula across the following two columns and down to the bottom of the time series. You will have to delete the formulas from cells in the log(Lemna) column that refer to empty Lemna cells. Check a few of the remaining copied formulas to ensure that the cell references have been correctly altered.

Now create two graphs and place them sidebyside to the right of the log(linear) column. The first should include graphs of Lemna, Unlimited, and Linear versus time. The Unlimited and Linear values should be represented by different kinds of lines with their markers turned off. The real Lemna values should appear as large datapoints unconnected by lines (increase the size of those markers above the Excel default). If you formulas are correct, then the Linear and Unlimited lines should both pass through the two Lemna points used to calculate the relevant rates. Correct your formulas if this doesn't occur. The second graph should represent the log values of the three data series versus time. You can use the Unlimited graph as a visual check on the accuracy of your formulas. What pattern would you expect for it in this semilog plot? Would you expect the lines on these graphs to go through the two data points used to calculate the rates? (Graphing hint: The fastest way to generate your second graph is to copy and paste your first graph next to itself and then alter the data series that apply to it. You only need to change two letters in the Y data series description of each line to complete the transformation of the data series, but you will also have to adjust the caption on the Y axis to reflect the change to log units.)

Now try an alternative method of creating a semilog graph. Copy and paste the standard graph below itself, open the Xaxis format menu, select the Scale tab, and click the box that says "logarithmic scale". You now have a semilog plot with one advantage over the one you created earlier: the numbers on the logarithmic axis are powers of 10 of the original variable, so the values are clearly meaningful compared to the logarithms that lie on the Y axis of your previous semilog plot (and you don't have to change the axis title). The only disadvantage of using a logarithmic scale instead of plotting the logarithms themselves is that the maximum and minimum Y values allowed by Excel on logarithmic scales both have to be integer powers of 10. For data series whose values all lie in a range much narrower than one order of magnitude, or whose range extends just a little above or below a unitary power of 10, much of the graph space lies empty and the data are not well displayed in Excel.

Make sure one of your graphs includes a legend. Alter each of the graphs to conform to the Bio205 graphing standards (allowing for alterations specifically directed in these instructions) and to display the data most effectively. Save your file with a name that meets the specifications at the top of these instructions, and submit your data file to bio205@utm.utoronto.ca. Don't forget to copy the submission email to yourself and CHECK THE ATTACHED FILE WHEN IT COMES BACK TO YOU!
