Nutritional Symbiosis Excel Spread Sheet Set Up
symbiotic teamdata asymbiotic teamdata
# energy packets -1 time (s) rate# energy packets time (s) rate
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
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
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.
SAMPLE DATA: SYMBIOTIC vs.ASYMBIOTICCORALS
symbiotic racedata asymbiotic racedata
# energy packets -1 time (s) rate # energy packets time (s) rate
symbiotic summaryasymbiotic summaryMean0.211Mean0.146
Standard Error0.013988016Standard Error0.0104011Median0.207142857Median0.1491383
Standard Deviation0.034Standard Deviation0.025Sample Variance0.001173988Sample Variance0.0006491Kurtosis0.335402889Kurtosis-1.757544
0.2110.146 meansRate of Energy Gain - 0.0340.025 std devSymbiotic vs. Asymbiotic
symbiotict-Test: Two-Sample Assuming Unequal Variances
Hypothesized Mean Difference0
P(T<=t) one-tail0.00236963energy packets per st Critical one-tail1.833113856
P(T<=t) two-tail0.0047392590t Critical two-tail2.262158887
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.
keep - symbiont teamdata expel - symbiont teamdataplayer
# energy packets -1 time (s) rate# energy packets - 0.5 time (s) rate
SAMPLE DATA: STRESSED CORALS -KEEP vs. EXPELSYMBIONT
keep - symbiont teamdata expel - symbiont teamdata
# energy packets -1 time (s) rate# energy packets - 0.5 time (s) rate
keep symbiont summaryexpel symbiont summaryMean0.072423829Mean0.106804
Standard Error0.003980875Standard Error0.0043182Median0.072195641Median0.1031991
Standard Deviation0.009751113Standard Deviation0.0105773
Sample Variance9.50842E-05Sample Variance0.0001119Kurtosis1.974573933Kurtosis3.2176709Skewness0.296065155Skewness1.6374284Range0.03026428Range0.0305128
Rate of Energy Gain for keep symbexpel symbStressed Corals:0.0724238290.106803969 meansKeep vs. Expel Symbiont0.0097511130.010577266 std dev
keep symbiontt-Test: Two-Sample Assuming Unequal Variancesexpel symbiont0.15
keep symbexpel symb
Hypoth Mean Diff0
P(T<=t) one-tail8.03911E-05energy packets per st Critical one-tail1.812461505
t Critical two-tail2.228139238
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.
symbiotic stressed - stressed - asymbiotic
keep symb expel symb
Rate of Energy Gain
stressed coral0.2keep symbiont