Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair, Lab Reports of Ecology and Environment

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

Pre 2010

Uploaded on 08/16/2009

koofers-user-wki-1
koofers-user-wki-1 🇺🇸

10 documents

1 / 4

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Ecology Lab! Biol 251
- 1 -
Data Manipulation with Excel*
DATA ENTRY
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.
FILTERS AND SORTING
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.
CREATING SUMM ARY INFORMATION
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.)
pf3
pf4

Partial preview of the text

Download Data Manipulation and Analysis in Ecology Lab using Excel - Prof. Brent Blair and more Lab Reports Ecology and Environment in PDF only on Docsity!

Data Manipulation with Excel

DATA ENTRY

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.

FILTERS AND SORTING

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.

CREATING SUMMARY INFORMATION

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.

CREATING GRAPHS

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.

Data Manipulation with Excel

HomeWork!

Use the techniques provided above to produce figures appropriately displaying THREE of the

following relationships. Graphs must have labeled axis and be easily interpreted by the reader.

Formatting is important! e.g., if you are printing in b/w make sure your graph doesn’t depend on

color for its interpretation.

For each figure you create, provide a sentence or two describing the pattern produced by the

data. In other words, what is each figure telling you?

1. Treatment (Shade/No Shade) vs. Dry Plant Mass

2. Dry Plant Mass vs. Percent Cover

3. Diversity vs. Species Richness

4. Treatment (Shade/No Shade) vs. Species Richness OR Diversity (you chose one)

5. Treatment (Shade/No Shade) AND Distance vs. Diversity

6. Treatment (Shade/No Shade) AND Distance vs. Species Richness

7. Distance vs. Light in plots 8 and 2

8. Distance vs. Species Richness in plots 8 and 2