IB 153L – Field Methods in Ecology September 8, 2003Data Entry and Analysis using ExcelThis lab will introduce you to data entry and analysis using Microsoft Excel, a popularspreadsheet program. The spreadsheet features of Excel allow for fairly simple data entry andorganization. Excel also has some basic analysis and graphing tools that are useful forinterpreting and presenting your ecological data. We will use other programs and learn morecomplicated statistical tests as we progress through the semester and as we collect other forms ofdata. Today’s lab will serve as “basic training,” ensuring a level of familiarity with dataorganization and interpretation that will be indispensable for other analyses this semester.We will go through these exercises as a class, discussing shortcuts or techniques alongthe way. If you are already familiar with Excel, try to experiment with ways to make the dataentry or analysis easier. If you are less familiar with Excel, take advantage of this opportunity tolearn some of the ways this program will help you organize and analyze your data. Please do nothesitate to ask questions or consult some of the Excel Cheat Sheets we have provided.Data CollectionEach field trip we will collect various data using different field methods. Today you willalso collect data to generate a dataset, but the data are, perhaps, less ecological:Student Information: Initials or Code Name: Gender: Age: Left Hand Finger Length: Finger 2: (nearest 0.1 centimeter) Finger 4: Finger 5: Height (nearest centimeter): Data EntryOpen MS Excel and begin a new worksheet. Label the active worksheet with anappropriate title that will always remind you that it is your main data. Enter the class’ dataremembering that it is important to keep each data point in a unique row as one record. Labelyour columns clearly, and fill in every column for each data point.Use the FILL option where appropriate to save you time (note that with other datasetsyou may also find the SORT and FILTER options useful). Remember that most ecologicaldatasets you encounter will be much larger than this one. How will you make your data entryeasier? How will you check for errors?Looking at Your DataUse the INSERT CHART command, or click on the chart icon to enter the chart wizard.First make a scatter plot of your data with the length of finger 2 on the y-axis and height on thex-axis. Take your time with the chart wizard so that you are very comfortable with the differentoptions you have. Make sure you learn how to choose which data you want to use (you mayhave many other columns of data on a page that you don’t want in your graph), or how to changefeatures of the graph.Use the FILTER or SORT command to look at your finger length data in a manner thathelps you decide on suitable “bins” for a histogram. What’s the different between using SORTand FILTER? Which is more likely to scramble your data if you’re not careful? You will findthe histogram option under the TOOLS menu in the ANALYSIS TOOLPAK. You will noticefrom the dialog box that you need to type the bin cut-offs for the histogram in an area of yourspreadsheet. Do this, and then return to the histogram dialog box. Create a histogram looking atthe frequency distribution of the lengths of all the fingers measured in the class. Calculate thefrequencies and have Excel produce a chart. Use the chart formatting options to make your chartlook decent (adjust the size of the chart, change labels or colors, etc.). How would you useFILTER to quickly change the data to make a histogram of the distribution of lengths of justfinger #4?Data Analysis I: Descriptive StatisticsExcel will calculate some simple statistics for you. Many descriptive statistics also canbe easily calculated using formulas. A cell entry that begins with an equal sign (=) indicates thatwhat follows is a function. The function can contain references to other cells on the page, tocells that are in a position relative the cell containing the formula, and it can also containmultiple steps in a calculation by using parentheses and a logical progression through thecalculation.Clicking on INSERT FUNCTION will give you a dialog box with all the possiblefunctions, and will show a short description of the function to help you decide whether it isappropriate for your purposes. Once you choose a function, Excel will tell you what informationis needed (data input, etc) to complete that function. Or, you can just type in the name of thefunction if you are already familiar with it. Calculate the mean, variance and standard deviationof one of your data columns using several different formulas. How can you copy these formulasto make similar calculations of other data columns?Use the Analysis Toolpak to calculate descriptive statistics for your data.Data Analysis II: T-testsA t-test tests whether the means of two groups are statistically different from each other(e.g. is there a difference in mean length of finger #5 between men and women in this class?).You can also test whether the mean of a group is statistically different from the mean of a greaterpopulation (e.g. is the mean height of students in this lab different than the mean height of allstudents at Cal?). First you should test whether the variances of the two samples are equal. Usethe Analysis Toolpak to calculate t-tests, both paired and unpaired. Take the results of your testof equal variance into account when choosing the t-test options in the Analysis Toolpak. Inwhich situations would you use a paired test instead of an unpaired test? When is your test one-tailed instead of two-tailed?Putting it All TogetherWe need your help looking at a data set. A student collected data on juvenile whiskerlength of an endangered species of catfish. The length of whiskers is very important in thisspecies because it affects the juvenile’s ability to keep its balance and swim correctly. Withwhiskers that are too long, the juveniles are more likely to swim lost in circles, unable to eat, andbecome vulnerable to predators. A recent study showed that a pollutant common in lawnfertilizer causes body parts to elongate in other species of catfish. Several of the study ponds arenear golf courses, while others are in nearby protected areas. Horrified that the golf courses maybe contributing