Excel Help Sheet for Statistical Tests

Directions: Open up the Daphnia Lab Excel spreadsheet found on the HW site. Save the spreadsheet to your home drive and be sure to email it to yourself. Also note that whenever you see quotation marks “ “ used in the directions for this exercise, do not actually type the “ “ into the Excelcells.

Calculating Averages: Do this for Blocks 1,2,3,5,6,7 and “all classes” by:

  1. Clicking on the 1st tab at the bottom of the spreadsheet labeled “1st”.
  2. Then click in the cell under the heading “avg. bpm”.
  3. In the formula bar type “=average(“ and highlight cells C9 through K9
  4. Then type “)” and hit Enter.
  5. Do this for 2%, 5% alcohol, 1%, 2% caffeine and the controls cells for all class tabs at the bottom of the spreadsheet

The ANOVA Test

ANOVA is a statistical test used to determine if there is a significant difference between the bpm means of the various treatments for ethanol and also would be used for the caffeine treatments to determine significance. If the ANOVA test produces a p-value that is less than 0.05, then there is meaningful difference among the means and you would reject your null hypothesis and accept your alternate hypothesis. If the p-value is greater than 0.05, then there is no significance between the means of the chemical treatments and you would accept your null hypothesis. To run the statistical test, following the provided steps in the Excel program.

  1. To obtain a P value, use the “ANOVA: Single Factor” function. To do this, in Excel click on “File” then “Options”, then “Add-ins” (it’s on the left panel, 2nd from bottom).
  2. Click “Analysis Toolpak” and then click “Go”.
  3. You will then click Analysis Tookpak again and click “Okay”.
  4. Click on “Data” tab from top menu, and then all the way over to the right click on the icon above the “Analysis” option.
  5. Choose “ANOVA: Single Factor” from the top of the list and then “okay”
  6. When the popup box appears with the cursor flashing in “Input Range”, use the mouse to highlight all the cells in the rows for alcohol, for example, for 1st block it would be B9 through K11 (be sure to include the headings 2%, 5%, control), check the box “Labels in First Row”, follow by clicking the “rows” button and then click “okay”
  7. The P value will appear in a new “Sheet” so click the “Sheet” tab at the bottom of the spreadsheet. Record the P-value in your spreadsheet in the yellow cell provided.
  8. If the p-value is below 0.05, perform the necessary t-test.
  9. If not, perform ANOVAs for the other 4 classes, by clicking on the tabs below.
  10. You will report this data in the Results section of your final paper to be submitted at a later date. The meaning of the data will be discussed in the Discussion/Conclusion section of your paper.

Paired t-test: A paired t-test is only used when comparing 2 sets of data. DO NOT do the t-test if your p-values for the ANOVA were above 0.05.

  1. To find the p-value of the given data, click on the cell highlighted in yellow for the treatment of interest, for example, for 1st block, cell S10.
  2. Click on “Formulas”, then “More Functions”, “Statistical”, then “TTEST”
  3. In the popup box, the cursor will be flashing in “Array 1”, now use the mouseto highlight the numbersin the row. For example, 1st block 2% alcohol, click and drag to highlight the numbers in cells C9 through K9.
  4. Now click in the box “Array 2”, then use the mouse to highlight the numbers in the row for “control” which would include C11 to K11.
  5. Enter 1in the boxes labeled “Tail” and “Type”, click “okay”
  6. Your P value will now appear in the cell, remember a P value of less than 0.05 is considered a significant difference. Be sure you can explain what this means in terms of the null hypothesis. See stats ppt on hw site if you do not know.