


Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Instructions for university students in biol 251 lab course on how to manipulate and analyze data using excel. Data entry, formatting, filters and sorting, creating summary information, and creating graphs. Students will learn how to calculate mean and standard error, create bar charts, xy scatterplots, and line graphs to interpret ecological data.
Typology: Lab Reports
1 / 4
This page cannot be seen from the preview
Don't miss anything!
Data may be entered directly or cut and pasted from other Excel files or files from other programs. Before entering data, you should always consider the objectives of using a particular data set; columns should be designed according to the manner in which data will be used &/or collected. In this exercise, you will be copying and pasting data from a text file into Excel. There should be a text file named “Competition Data” on the desktop of your computer. Open that file and copy the complete data set. Paste these data into Excel using the EDIT …Paste Special…Text command (at right). The data can then be formatted such that the sheet is easier to read by highlighting column headers, re-sizing columns, centering text, etc. You will notice columns for Plot, Distance, Treatment, Shade, Light, Plant mass, Percent Cover, Diversity, and Species Richness. The two statements at the top of the sheet explain the origin of these data. Under Treatment, S = shaded, U = unshaded.
Depending upon the information one wishes to derive from the data, it may be necessary to re-sort the data or to extract portions of the data to be pasted into another worksheet for manipulation. To sort or filter, open the DATA menu on the upper toolbar, and select the appropriate option. First, let’s re-sort the data to group together the shaded and unshaded treatment data. In order to do this, you must highlight the data set. Now, select DATA …sort…and sort by Treatment, then by Plot, then by Distance, all in ascending order. Also be sure to indicate whether you have included your header row in the selected data to be sorted. Now, click OK and the data will be sorted. We now can calculate summary data for the two shade treatments from the raw data in each of the last six data columns.
Two of the most useful pieces of summary data you can obtain from Excel are a mean and variance estimates from each group of data to be used in the laboratory exercises. The typical variance estimate many scientists use is the “Standard Error.” Standard error (SE) is computed as the standard deviation of a dataset divided by the square root of the number of values in the set. *This lab was adapted from Using Excel, Basics ( 2005 ) by Gary Ervin’s (Mississippi State U.)
To calculate the mean and SE of a group of data, such as shade, let’s first copy and paste the data and grouping variables onto a new worksheet. Now, add a header for mean and SE. Type the formula for average into the cell beneath the header [=average (data)] and for SE into the appropriate cell [=stdev(data)/sqrt(n)]. The data range should include the first 16 rows of shade data. The last 16 rows will be summarized in the two cells adjacent to the first data for unshaded plots, in the same manner as above. This process can be repeated for all seven response parameter columns, preferably with each in a new, appropriately named worksheet. Create a new worksheet by clicking INSERT …worksheet. Name worksheets by double-clicking the worksheet tab at the bottom of the screen and typing a name.
Now that you have summary data that can be used to compare the two treatments, you are ready to create pictorial representations of those data to actually see how the treatments compare. In the shade data example, let’s see whether the experimental manipulation actually resulted in a real difference in the light environment among the plots. Bar Charts Click on the chart wizard icon in the middle upper of the top toolbar (it looks like a miniature bar graph). The top selection is highlighted (column chart); click NEXT. You now need to indicate which data to plot; select the Series tab, and click ADD. There are numerous ways to enter the data to plot. The easiest is to click on the small worksheet icon to the right of each empty data selection windows. You also can type the data directly into the window or type in the worksheet range where the data are located. For X-axis labels, you may simply type “Shaded,Unshaded” into the window. A preview of the chart in the upper portion of this dialogue box. NEXT , you have the option of modifying various of the chart features, including axis titles, gridlines, etc. To add the SE estimate, we will add error bars by double-clicking on one of the bars in the chart. This opens a dialogue box to Format Data Series. Click the “Y Error Bars” tab, and add SE the same way you added the means to be plotted. Bu sure to indicate that you want BOTH plus and minus error bars, and click OK.