Nutritional Symbiosis Excel Spread Sheet Set Up
The table above is your starting point to use MS Excel to process the students’ race data, graph the results and conduct a statistical analysis of the data. Put the computer cursor anywhere on the above table and left click twice. You should then see this table in an Excel workbook format with the rows 1-12 and columns A-G highlighted. Go to Edit and then Copy. Next, from the Start menu open MS Excel. Once Excel presents you with sheet 1 of a new workbook, simple go to Edit and then click on Paste. This procedure will place the above table above into Excel, from which you’ll follow the procedure outlined below to process, graph and analyze the students’ data. Before entering the students’ race results into the spreadsheet, you may need to expand some of the columns so that the entire heading is viewable. You can change a column’s width to match the width of its largest component by double clicking on line between the column you want to adjust and the column immediately the right of it. At the end of the procedure described below, you’ll find an example of a completed data set you can you as a guide.
Processing, Graphing and Analyzing Student Data
1. Enter into the data table the number of energy units and race times for each student.
2. Calculating the rate of energy acquisition for individual players on each team.
a. Click on the top cell of the rate column, then in the formula bar at the top of the page type =b4/c4 then hit enter. The rate for the first player should appear in the top rate cell.
b. Click and highlight the entire rate column covering all player for the symbiotic team, go to the “Edit” drop down menu at the top of the page and click on “Fill” and then “Down”. The rates for the players should now fill the highlighted portion of the column.
c. Repeat 2a (type =f4/g4 in the rate cell for the first player on the asymbiotic team) & 2b above with the rate column for the asymbiotic team.
3. Generating the summary statistics for each team.
a. Click on the “Tools” drop down menu at the top of the page and look for “Data Analysis” at the bottom of this drop down menu. If Data Analysis does not appear, click on “Add-Ins”, click on “Analysis Toolpak” and then click OK. Data Analysis should now appear at the bottom of the Tools drop down menu.
b. Now click on Data Analysis on the Tools drop down menu. Click on “Descriptive Statistics” and then the “OK” button. Highlight the rates for the symbiotic team (these cells will automatically appear in the input range box for the data analysis). Check the “Summary Statistics” line then click OK. The summary statistics will appear on a new worksheet (probably worksheet 4).
c. Repeat 3b with the asymbiotic team’s rates, the summary statistics for this team will appear in a new worksheet (worksheet 5)
4. Moving the summary statistics for each team to Sheet 1 where the raw data were recorded.
a. Go to worksheet 4 and high light both columns of the summary statistic (both the description and values) and then click copy under the “Edit” drop down menu at the top of the page. Click on the worksheet 1 tab at the bottom of the screen. Beginning at least 2 cells under the bottom of the time column for the symbiotic team, highlight two column and 12 rows. Then go to the “Edit” drop-down menu and click paste. The summary statistics for the symbiotic team should appear under the symbiotic team’s data.
b. Repeat 4a with the summary statistics for the asymbiotic team placing these summary statistics under the asymbiotic team’s data.
5. Moving the mean and standard deviation for each team to a 2x2 block for graphing.
a. From the summary statistics for the symbiotic team, highlight and copy the mean and standard deviation (SD) just below it. Go to the “Edit” drop-down menu and click on copy. Two cells below the summary statistics for the symbiotic team, highlight two cells in a single column, go to “Edit” and “Paste Special”, check “Values” and then OK.
b. Repeat 5a above and copy the mean and SD for the asymbiotic team immediately next to the symbiotic mean and SD so that the two sets of data form a 2x2 block of cells.
6. Graphing the data.
a. Highlight the means for the symbiotic and asymbiotic teams (should be immediately adjacent to one another).
b. Click on the “Chart Wizard” icon (the little graph) at the top of the page. Under “Chart Sub-type” click on the upper-left type and then click “Next”. You should now see a pop-up box with your graph that you’ll customize in the next few steps.
c. Your graph should contain two bars, one for the mean of each team. If this is the case, click on Next in the Chart Wizard. If not, click on Back and find out where things went wrong.
d. In the next box of the pop-up, you can add a Chart Title, give your y-axis a label, remove the grids, etc. When you’ve added and deleted the desired chart features, click on Next. In step 4, you’ll tell the Excel program where to put your new graph. Your options are (i) as an object on your data page (sheet 1) or (ii) a new page will be created (Chart 1) where your new graph will be located.
e. Further customization can now be done to your graph, the most important of which is to add the SD bars to the mean for each team. Place your cursor on one of the two bars and click the right (not left) button on your mouse. In the pop up box go to the “Y error bars” tab. Click on the “Plus” box and then below on “Custom”. You’ll now click on the sheet 1 tab at the bottom of the page to get to the 2x2 block of cells with the means and the standard deviations. Highlight the two SDs, which should be in the 2 adjacent cells in the bottom half of the 2x2 block. Symbols for these cells will now appear in the “+” window to the right of Custom. Click OK and then you should see the SDs added to the top of each mean bar. You’ll also see other ways to customize your graph under this pop up window. Other changes can be made by placing the computer cursor on any part of your graph (i.e. an axis or simply the space between the bars) and then give mouse a right click. Explore your options!
7. Statistical analysis of your data.
This step will determine if the difference in the mean rate of energy acquistion for the symbiotic and asymbiotic teams is significant, which implies that under an accepted set of scientific rules governed by strict mathematic principles, you have the confidence to support your alternative hypothesis that the symbiotic and asymbiotic teams differed in their rates of energy acquisition. For your experiment (= the race), the appropriate statistical test (mathematical formula to compare the teams’ results) is the t-test, which is used when comparing data from only two teams or “treatments”. An ANalysis Of VAriance (= ANOVA) is used with three or more treatments. These analyses takes into account the magnitude of the difference between the means and the magnitude of the scatter of the individual data points used to calculate the mean. Lots of individual data points lying far from the mean leads to a large standard deviation, which means to detect a significance difference between the means, the difference between means must be relatively large.
a. To conduct the t-test, go to the Tools menu and click on Data Analysis. Scroll down to “t-Test: Two-Sample Assuming Unequal Variance”, then click OK. In the pop up box, you’ll input the rates for each team into the appropriate “Variable Range” box. Put the cursor in the “Variable 1 Range” box, then highlight the rates for the symbiotic team. These rates will automatically be listed as the input data for variable 1. Next put the cursor in the “Variable 2 Range” box and then highlight the rates for the asymbiotic team. Where this pop up asks for the “Hypothesized Mean Difference” put 0 (zero), then click OK. The results of this statistical analysis will appear on a new sheet in the workbook. Look for the number listed under P-value. The P-value basically tells you what proportion of the time you could expect the values you record (and thus the means and SDs) to occur by random chance. If the appropriate statistical test tells you that you could expect the results you recorded less than 5% of the time (P = 0.05), then the difference between your means is “significant”. Thus a P-values less than or equal to 0.05 is accepted to be meaningful.
Data Sheet for the Stressed Coral Experiment
To use this data sheet by copying it to an Excel spreadsheet follow the instructions at the top of the page. Data input, processing and statistical analysis follows the same general procedure described above.
To compare the rate of energy acquisition among the 4 different treatments, run an 1-way ANOVA. To do this analysis, place the individual rates in 4 columns, one for each of the 4 groups: (i) non-stressed with symbiont; (ii) non-stressed without symbiont; (iii) stressed keep symbiont; (iv) stressed expel symbiont (see example below). Under the Tools drop down menu, go to Data Analysis. Click on ANOVA: Single Factor and then OK. To put the data into the Variable Range box, simply highlight all four columns simultaneously. These will automatically be added to the Variable Range box then click OK. The ANOVA table will appear in a new worksheet. For the ANOVA, a P-value of 0.05 or less tells you there is a significant difference among the data set, but it would tell you which one differ significantly. Which data set differ significantly has to be determined by a “post-hoc” test. These test, unfortunately are not available from the standard Excel Analysis Toolpak. Place the means and SDs as indicated below and use the Chart Wizard to create a figure with all four data sets represented, as has been done with the samples data below.
6