DOC

Nutritional Symbiosis Excel Spread Sheet Set Up

By Hector Duncan,2014-04-09 18:15
10 views 0
Nutritional Symbiosis Excel Spread Sheet Set Up

    Nutritional Symbiosis Excel Spread Sheet Set Up

     symbiotic teamdata asymbiotic teamdata

    player

    # energy packets -1 time (s) rate# energy packets time (s) rate

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    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.

     1

    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.

     2

    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

     3

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.

4

    SAMPLE DATA: SYMBIOTIC vs.ASYMBIOTICCORALS

     symbiotic racedata asymbiotic racedata

    player

     # energy packets -1 time (s) rate # energy packets time (s) rate

    19350.257142910620.161

    29450.210790.127

    39570.157894710580.172

    49380.236842110910.11

    59420.214285710730.137

    69450.210590.169

    7

    8

    9

    10

    symbiotic summaryasymbiotic summaryMean0.211Mean0.146

    Standard Error0.013988016Standard Error0.0104011Median0.207142857Median0.1491383

    Mode0.2Mode#N/A

    Standard Deviation0.034Standard Deviation0.025Sample Variance0.001173988Sample Variance0.0006491Kurtosis0.335402889Kurtosis-1.757544

    Skewness-0.27874075Skewness-0.385183

    Range0.09924812Range0.0625237

    Minimum0.157894737Minimum0.1098901

    Maximum0.257142857Maximum0.1724138

    Sum1.266165414Sum0.8766543

    Count6Count6

     symbiotic asymbiotic

    0.2110.146 meansRate of Energy Gain - 0.0340.025 std devSymbiotic vs. Asymbiotic

    symbiotict-Test: Two-Sample Assuming Unequal Variances

    asymbiotic0.3symbioticasymbiotic

    Mean0.2110275690.1461091

    Variance0.0011739880.0006491

    0.2Observations66

    Hypothesized Mean Difference0

    df9

    t Stat3.7242674540.1

    P(T<=t) one-tail0.00236963energy packets per st Critical one-tail1.833113856

    P(T<=t) two-tail0.0047392590t Critical two-tail2.262158887

     5

    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

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

     6

SAMPLE DATA: STRESSED CORALS -KEEP vs. EXPELSYMBIONT

     keep - symbiont teamdata expel - symbiont teamdata

    player

    # energy packets -1 time (s) rate# energy packets - 0.5 time (s) rate

    191020.08823539.5750.127

    27990.07070718.5830.102

    381150.06956529.5870.109

    47950.07368427.5730.103

    581380.0579718.5820.104

    691210.07438027.5780.096

    7

    8

    9

    10

    keep symbiont summaryexpel symbiont summaryMean0.072423829Mean0.106804

    Standard Error0.003980875Standard Error0.0043182Median0.072195641Median0.1031991

    Mode#N/AMode#N/A

    Standard Deviation0.009751113Standard Deviation0.0105773

    Sample Variance9.50842E-05Sample Variance0.0001119Kurtosis1.974573933Kurtosis3.2176709Skewness0.296065155Skewness1.6374284Range0.03026428Range0.0305128

    Minimum0.057971014Minimum0.0961538

    Maximum0.088235294Maximum0.1266667

    Sum0.434542973Sum0.6408238

    Count6Count6

    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

    Mean0.0724238290.106804

    Variance9.50842E-050.00011190.1Observations66

    Hypoth Mean Diff0

    df100.05t Stat-5.85379045

    P(T<=t) one-tail8.03911E-05energy packets per st Critical one-tail1.812461505

    P(T<=t) two-tail0.0001607820

    t Critical two-tail2.228139238

     7

    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.

     8

     CORAL TYPE

    symbiotic stressed - stressed - asymbiotic

     keep symb expel symb

    player

    10.2571428570.0882352940.1266666670.16129032320.20.0707070710.1024096390.12658227830.1578947370.0695652170.1091954020.17241379340.2368421050.0736842110.1027397260.1098901150.2142857140.0579710140.1036585370.13698630160.20.0743801650.0961538460.169491525

     mean0.2110275690.0724238290.1068039690.146109055

     std dev0.0342635020.0097511130.0105772660.025477369

    Rate of Energy Gain

    symbiotic coral0.3

    0.25

    stressed coral0.2keep symbiont

    0.15stressed coral

    expel symbiont0.1