BI163 LABORATORY

Comparative Statistics with Excel

 This self-guided tutorial will show you how to use basic functions of Microsoft
Excel to make objective, quantitative comparisons between different
experimental groups.

 Before proceeding you must read and understand the general concepts
presented in the separate handout Working with Statistics.

 This tutorial will cover the use and interpretation of p-values derived from a t-
test (a common comparative statistical test).

 This tutorial is designed to be used in conjunction with the Excel Sample Data
File included as part of this module. It presumes you can apply the skills learned
previously in the Data Analysis and Presentation Module.

 When you are finished, you must submit your work via the assignment drop
box provided on Moodle.
 Part 1: Why Comparative Statistics?

This question is best answered with a

simple example.

Let’s say you toss an ordinary coin one-

hundred times. Do you have any predictions
or expectations regarding the outcome of
this activity?

Image source: [Link]

You probably predicted there would
be fifty heads and fifty tails.

However, you might not necessarily

expect that there would be exactly 50 50
fifty heads and fifty tails every single
time you repeated the activity.

For example, you would probably still

be okay with an outcome of 52 heads
and 48 tails.
52 48
Image source: [Link]
But how would you feel about an
outcome of sixty heads and forty tails?
Still okay? Not sure?
60 40
You probably see where this is going…

What about seventy-five heads and

twenty-five tails? By now you are almost
certainly starting to become suspicious
of the coin, or the coin tosser!
75 25
You have just demonstrated that you already have an
intuitive understanding of comparative statistics!
 You have certain expectations of what you observe.

 You know there is variation in nature, and that random chance may
cause your observations to differ from your expectations.

 You know that the more your observations differ from your
expectations, the lower the likelihood (or probability) that random
chance can explain that difference.
 But where do you draw the line? As a scientist, your intuition is not enough!
Comparative statistics provide an objective method for quantifying the effects of
chance.

 Comparative statistics are mathematical tests that take into account the location,
variability, and size of our samples in order to calculate the probability that observed
differences are the result of chance.

 As scientists we are skeptics. Typically, it is only when our statistical tests tell us that
the probability (p) of a certain outcome resulting from chance alone is less than or
equal to 5% (≤0.05) do we state that the differences we observe are significant.

 While this may sound like semantics, the distinction is important. Observed
differences that are not statistically significant may be attributed to chance, and
chance should not be used to draw scientific conclusions!
 Part 2: Performing a T-test
• Open the Sample Data File in Excel. It should
resemble that shown to the right.
• In this experiment, we are trying to determine
the effect of two different growth hormones on
plant height.
• The data includes measurements of plant height
taken from three experimental groups. The first
group is a control; these plants were not treated
with any hormone. The remaining groups were
treated with plant growth hormones A and B
respectively.
• The experiment is replicated eight times.
• Use the skills you learned previously to analyze
and present these data in a form suitable to a
scientific paper (i.e., using descriptive statistics
and graphics).
• Your outcome should resemble that
on the right.
• Examine your results. Notice that
while the averages differ among
groups, there is also quite a bit of
variation within each group.
• You might have even noticed that
there is some overlap in the
measurements of the different
groups.
• This creates a challenge. At first
glance you may conclude hormone A
caused plants to grow taller than
untreated plants, while hormone B
caused pant to be shorter.
• However, what if those differences
you observe are just the result of
chance? Would your conclusions be
justified?
• Here is where comparative
statistical tests help.
• Remember, such tests tell us the
probability that the differences we
observe result from chance.
• The t-test is a common statistical
test that can be used to compare
two experimental groups.
• In our example, we want to
compare plants in both hormone
treatments to the untreated
control plants.
• The t-test will tell us if the
differences are statistically
significant.
• Click on the image to the right to
start a short video showing how t-
tests are conducted in Excel. This
involves:
 Inserting a formula
 Selecting the two arrays (groups) you
want to compare
 Choosing a two-tailed test
 Choosing the correct type of test--in
this case, a two-sample test with
unequal variance
• Pay careful attention to these last
two parameters as they can have a
dramatic effect on your results!
• After watching the video, duplicate
the process in your own Sample
Data File before proceeding.
• The value returned by the TTEST
function is the p-value. This
represents the probability that
chance alone could explain the
differences observed between the
control and treatment A.
• In this case the p-value is quite
small (0.002).
• Remember, as a convention, we
regard probabilities of 5% or less
(i.e., p≤0.05) as indicative of a
significant difference.
• In your Sample Data File, conduct
another t-test, this time comparing
hormone treatment B to the
control.
• You should find that the p-value
comparing the control to hormone
treatment B is also quite small
(0.001).
• In summary, we would interpret
these results as showing that plants
treated with hormone A were
significantly taller than control
(untreated plants), and that plants
treated with hormone B were
significantly shorter than control
plants.
• Note that we are not yet drawing
any conclusions about the effects of
these hormones. We are only
making objective statistical
comparisons between
experimental groups.
 Part 2: Presenting Statistical Results
• Before proceeding, paste your graph into a Word
document. Make it a proper figure by adding an
appropriate caption.
• As implied in the previous slide, comparative
statistics—and their associated interpretation—
are considered part of your evidence. As such,
they are presented in the results section of a
scientific paper, along with your figures.
• Next we will write a short statement reporting
the results of our t-tests.
• Statements can vary in their wording, but in general
should include the following elements:
 A clear statement of what exactly was compared.
 A clear statement of whether the observed differences were
significant or not.
 If the differences are significant, a clear statement of how they
are different in terms of both direction and degree.
 An indication of the test used, and the p-value returned by that
test. (Specific formats vary for different tests but this will suffice
for now.)
• For our example, study the following statements.
Note how they incorporate the elements listed
above.
Plants treated with growth hormone A were significantly taller by
32% than untreated control plants (t-test, p=0.002). Plants treated
with growth hormone B were significantly shorter by 32% than
untreated control plants (t-test, p=0.001).
• As shown to the right, add similar statements to
your sample report. Place them under the header
Results and add a parenthetical reference to Figure
1.
A few more tips…
• Note also what the statements do not include:
 An explanation of how to interpret p-values. You do not need to explain that “the difference is significant because the
p-value is less than 0.05.” This is generally understood and accepted.
 Any reference the “null” or “alternative” hypotheses. While these concepts are helpful for understanding how
comparative statistics are applied, they are also generally understood and do not need to be explained.

• Lastly, make an effort to round your reported p-values appropriately. Excel is just a calculator—
while it may report values to several decimal places, not all of those figures are meaningful. For
example:
o If p=0.71264789543, write p=0.71
o If p=0.00916745, write p=0.009 or just p<0.01
o If p=0.00000000175974563, just write p<0.001 or even p<<0.001
Don’t fret too much about this, just pay attention to your presentation and apply a little
common sense!
 Part 3: Drawing Conclusions
• Remember, your descriptive statistics, figures and
comparative statistics constitute evidence. They
are not conclusions in and of themselves.
• Simply put, a conclusion is an answer to your
experimental question, based on your empirical
evidence.
• While evidence consists of observations,
conclusions often suggest a cause-and-effect
relationship based on those observations. The
difference here can be subtle…
Saying “Plants treated with hormone A grew taller than control
plants” is an observation.
Saying “Hormone A causes plants to be taller” is a conclusion.
• As shown to the right, add a conclusion to your
sample report that is supported by the evidence
presented in the results.
• Save your Word document and select the Effect of
Variation tab at the bottom of the Excel spreadsheet.
This is a similar data set to the one you just analyzed,
but the numbers are a bit different.
• As you did previously, calculate descriptive statistics
and present these results in an appropriate figure. Add
this new figure to your sample report in Word.
• Before proceeding, take careful note of how the
averages and standard deviations of these data
compare to those in the previous exercise. What is the
same? What is different?
• As you did previously, run t-tests comparing each
treatment to the control. Again, note how your results
compare to the previous exercise. Would these results
lead to the same conclusions regarding the effects of
the two growth hormones?
• Before proceeding, add results and conclusion
statements to your sample report.
In comparing this exercise to the previous one, you should have noted the following:
• The averages are the same, but the variation (standard deviation) in the second group is much larger.
• The t-tests return higher p-values. These p-values indicate that the observed differences are NOT
significant!
• With different statistical evidence, different conclusions about the effects of growth hormones A and
B need to be drawn.

***If your report does not reflect these points, review and edit it before proceeding!***

What does this exercise tell you about the potential effect of sample
variability on your conclusions?
Would it have been okay to draw conclusions simply by comparing the
averages of the sample groups?
• Select the Effect of Sample Size tab at the bottom of the
Excel spreadsheet. This is a similar data set to the ones you
just analyzed, only with the sample size (n) increased from
eight plants per group to twenty-four plants per group.
• Analyze this data set in the same manner. Compare your
calculations and conclusions to the previous two exercises.
 What does this exercise tell you about the potential effect of
sample size on your conclusions?

When you have finished all the exercises, upload your three complete
figures with results and conclusion statements in a single Word
document using the assignment drop box provided on Moodle.