This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Page 1 of 5 INTRODUCTION TO EXCEL Means, Standard Deviation, Bar Graphs and Error Bars Calculating Means Consider the following study. An ecologist wanted to know how large are the home ranges of badgers (i.e. how far they typically move while foraging for food). There were some concerns that males, females and the juveniles might have different home range sizes, so 5 individuals from each group were monitored. Below are the results of this study (ranges are in km2). The question here is: do males, females and juveniles have similar or significantly different home ranges from one another? males females young 1.2 0.9 1.6 1.0 0.6 1.5 1.6 0.6 1.0 0.8 0.3 1.2 1.4 0.7 1.2 Computing means with Excel 1. Enter the data for badger home ranges into the spreadsheet. Set up the spreadsheet just as it is shown above: 3 columns with 6 rows each. 2. Move the cursor to an empty cell below the male column (skip one row to avoid later confusion). 3. Click on the function button (fx) in upper left above the spreadsheet. 4. Find and select average function (It’s under statistical functions). 5. In the Function arguments window, you see the words “Number 1". Click on the spreadsheet icon (SPREAD) just to the right of the open box associated with “NUMBER 1" 6. With your mouse, drag a box around the 5 numbers associated with the male badger data, then click on the Spread icon that should be visible somewhere within your spreadsheet 7. Then click on OK. The mean for male badgers should now be present in the cell highlighted in step 2. 8. Repeat steps 2 to 7 for female and young groups. Alternatively, you can copy and paste the function to the cells below females and young. Excel recognizes that you want to apply the function Page 2 of 5 Calculating Standard Deviation A similar procedure is required to compute the standard deviation for male badgers. In fact, you will follow steps 2‐7; the only difference is that you will choose STDEV instead of Average in step 4. The STDEV function may not be present in the menu, but in the Insert function window find the menu called Select a category, choose Statistical, then look for STDEV. Once you’ve become familiar with Excel formulas you will discover that it is often easiest to simply type in the formula. A formula must start with the equal sign (=), and can be followed by any built in Excel formula, or a string of Excel formulas. Page 3 of 5 Creating Bar Graphs with Error Bars around the cells containing the 3 averages (means). 2. Click the Insert tab, then the Column icon. 3. You will see a series of graph options. Choose the first 2‐D Column type. 4. Notice that the x‐axis is numbered from 1 to 3. We want to change that to “male”, “female”, and “young”. a. Assign axis labels by selecting the Design tab under the Chart tools menu. Then click on Select Data. In the Select Data Source window, click on Edit under the Horizontal (Category) Axis Labels heading. This will open a small window called Axis Labels from which you will select cells containing the labels “males”, “females”, and “young”. In this case those would be cells B1, C1, and D1. 4b. Select Axis Title, and then Primary vertical axis title, then rotated title. Then click on the box and add the appropriate label including units and the type of error bars. 5. Delete the figure legend by clicking on it to highlight then hit the delete button. Or select the Legend button under the Layout tab and choose No legend. 6. Instead of a title write a short, descriptive figure legend that appears below the figure. Do this by adding a text box. 7a. Add error bars: Under the Layout tab, click Error bars, then More Error Bars Options. 1. With your mouse, draw a box Page 4 of 5 7b. In the Format Error Bars window, select Both and Cap. Then select Custom and click on Specify Value. 7c. the small window Custom In Bars select the 3 values for Error the standard deviation. That’s all there is to it. Now you should have a bar graph with standard deviation error bars, a figure legend, proper x‐axis and y‐axis labels that include units and the type of error bars. 1.6 Home range size (km2) 1.4 1.2 1 0.8 0.6 0.4 0.2 0 males females young Figure 1. Average home range size (ha) with standard deviation error bars . Page 5 of 5 Interpretation of Graphs and Error Bars From this graph you should be able to make an approximate assessment of whether males, females and young differ in their home ranges. Take a look at the error bars of each of the demographic classes. Do they overlap? If so, then we can say there is no ‘significant’ difference in the home range size between the classes. For example, the error bars for the home range size of females and young do not overlap. Thus we can conclude: “There is a significant difference in the average home range size of females and young. Young had a larger home range size than females.” Alternatively, when you compare the error bars on male and young home range sizes, we see that the error bars do overlap. Thus we can conclude: “There is no significant difference in the average home range size of males and young.” ...
View Full Document
This note was uploaded on 03/21/2011 for the course BIOL 1201 taught by Professor Wishtichusen during the Spring '07 term at LSU.
- Spring '07