Excel Graph Paper Template - Free Download
4.3,
3019
votes
Please vote for this template if it helps you.
School Science Review, December 2001, 83(303) 23
Millar
Biology statistics made simple using
Excel
Biology statistics made
simple using
Excel
Neil Millar
Spreadsheet programs such as Microsoft
Excel
can transform the use of
statistics in A-level science
ABSTRACT
Modern spreadsheet software, such as Microsoft
Excel
, can transform the use of statistics in
biology. Instead of being difficult to do and to
interpret, statistical tests become simple to do
and much easier to interpret. This article des-
cribes when and how to carry out many of the
most common tests (including mean, standard
deviation, confidence limits, correlation, regress-
ion,
t
-test, χ
2
-test and ANOVA) using
Excel
.
Statistics is an area that most A-level biology students
(and their teachers!) find difficult. The formulae are
often complicated, the calculations tedious, degrees
of freedom mysterious, and probability tables
confusing. But in fact students need no longer grapple
with any of these. In real life, biologists and
statisticians rarely use calculation and tables these
days, but instead use statistical packages such as
Minitab or SPSS. But it isn’t even necessary to buy
an expensive statistics package, since spreadsheet soft-
ware such as Excel has most of the common statistical
tests built-in.
When using statistics, the first hurdle is to decide
which statistical test to use. Figure 1 (overleaf) is a
flow chart showing when to use the various tests
described in this article. There are many other possible
statistical tests, but this flow chart should be more
than sufficient for A-level biology students. It briefly
summarises the Excel formulae and how to interpret
the results, so it can be used as a handy guide on its
own once the student is familiar with the tests. This
flow chart should be used when designing an
experiment, not after the experiment is complete. This
will ensure that the correct kind of data are collected
so that the statistical test will be valid. The rest of the
article describes in detail how to carry out these tests
using Excel and how to interpret the results. It is
divided into five sections:
1 Descriptive statistics mean, median, mode
standard deviation,
standard error,
confidence interval
2 Graphing data scatter graphs, bar
graphs
error bars, lines
3 Association statistics Pearson coefficient,
Spearman coefficient
linear regression
4 Comparative statistics paired and unpaired
t
-test
Mann-Whitney
U
-test
ANOVA
5 Frequency statistics χ
2
-test
χ
2
-test of association
1Descriptive statistics
Most school biology experiments will involve some
kind of measurement, such as time, length, mass,
temperature, absorbance, etc., and in a well-designed
experiment there should be a number of repeats (or
replicates) of each measurement. Once some measure-
ments have been collected the first job is usually to
summarise them using descriptive statistics. Excel has
formulae for the three measures of the centre of a
distribution of replicates.
The arithmetic mean is given by the formula:
=AVERAGE (range)
The median is given by the formula:
=MEDIAN (range)
And the mode is given by the formula:
=MODE (range)
source: utdallas.edu
