i-biology excel statbook

7/31/2019 I-Biology Excel StatBook

1/73

Statistical Analysis, Data Collection and Processin Shadowed boxes are links to resources

Stephen Taylor. IBDP Biology Excel Statbook. From http://i-biology.net/ia/statexcel Last u

What do you

Compare the means of two sample population

Plot the change in a variable over time (the '5

Determine if there is a correlation between tw

Learn about error bars and variability in Biolog

Plot changes 'before and after' a condition is c

Compare outcomes to expected data (Chi-squa

Quick guide to tables and graphs (DCP)

More calculations for% differe

magnifica


2/73

and Excel

Links:

Front Page

i-Biology.net

pdated 27 Sept 2011 A Creative Commons work by

.....under one experimental condition

..under two (or more) experimental conditions

5'

y

anged

red test)

monohybrid cross

dihybrid cross

codominant cross

polygenic cross

..where individual trials are repeated

Learn more about the t- t-test practice

"5x5" means 5 increments of the IV over 5 times, with 5 repeats

ce vs %

tions & scales

Yeast population growth
http://i-biology.net/http://i-biology.net/http://i-biology.net/


3/73

Stephen Taylor
http://i-biology.net/


4/73

Variability and Error Bars in Biology Data sho

The mean is a

Data show va

We could use

Standard de

34% of data f

If a calculated

95% of all dat

Consider this

repeat

Population A

Population B

Image from:

Population A

Calculate mea

Click on the c

Change the ra

When descri

Error barsYou could use

The top grap

The bottom gThis is useful i

The larger the

Where there i

We can test t

Further usefu

Click4Biology

Error Bars

Error Bars in B

Can we tell how significant the difference is from the overlap in the error bars

repeat 1 2 3 4 5 6 7 8

Population A 1.0 2.0 3.0 4.0 3.0 2.0 1.0 2.0

Population B 2.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0

ttp://en.wikipedia.org/wiki/Standard_deviatio

DV (unit, uncertainty)

68%

95%

Pop A

(n=10)

Pop B

(n=10)

0.0

2.0

4.0

6.0

MeanValueofthe

DependentVariable

Comparing the means of Population A andPopulation B. Error bars show standard deviation.

Pop A

(n=10)

Pop B

(n=10)

0.0

2.0

4.0

6.0

MeanValueofthe

DependentVariable

Comparing the means of Population A andPopulation B. Error bars show 95% confidence.

le

Comparing the means of Populations A and B.

(Error bars represent 95% confidence)
http://click4biology.info/c4b/1/stat1.htmhttp://www.graphpad.com/articles/errorbars.htmhttp://jcb.rupress.org/content/177/1/7.full.pdf+htmlhttp://en.wikipedia.org/wiki/Standard_deviationhttp://en.wikipedia.org/wiki/Standard_deviationhttp://jcb.rupress.org/content/177/1/7.full.pdf+htmlhttp://www.graphpad.com/articles/errorbars.htmhttp://click4biology.info/c4b/1/stat1.htm


5/73

In the default data here, you can a small overlap, but significant difference.

What happens as the datasets become more similar or more different?

Population A

(n=10)

Population B

(n=10)

0.0

0.5

1.0

1.5

2.0

2.5

3.0

.

Meanvalueofdependentvaria

(unit,

)


6/73

variability.

measure of the central tendency of a set of data. It useful, but not by itself.

iability - this is how spread the data are around the mean.

the range of the data (max - min values) to show variability, but this is skewed by outliers.

iation (sd) is a very useful tool in descriptive statistics.

ll one sd above and one sd below the mean. Therefore 68% of all data fall within 1sd of the mean.

value for the sd is large, it suggests a lot of variability in the data. If it is small, there is less variability.

a fall within 2sd of the mean. We can use this for 95% confidence limits.

set of raw data:

1 2 3 4 5 6 7 8 9 10 mean

1.0 1.0 2.0 3.0 2.0 1.0 3.0 6.0 7.0 6.0 3.2

1.0 1.0 1.0 1.0 1.0 1.0 2.0 1.0 2.0 1.0 1.2

has a higher mean than Population B. Population A shows greater variability in the data.

n with the formula "=AVERAGE(data)" Calculate sd with the formula "=STDEV"

Calculate 95% confidence with the formula

lls to see the formula. "=CONFIDENCE.NORM(0.05, sd, sample size)"

w data in the table to see what heppens.

bing data, include the standard deviation as an indictor of variability.

are a graphical representation of the variability of a set of data. Go torange, sd, standard error or 95% confidence on the error bars. to practice

uses sd for the error bars. You can see a clear difference in variability.

raph shows 95% confidence.n determining the significance of a difference between the means.

overlap between 95%CI error bars, the larger the value of P (see t-test). A small overlap might still be a

s no overlap in 95%CI error bars, the difference is very likely to be significant.

ese deductions further by carrying out a t-test Link:

l resources:

by John Burrell Thanks also to Dave Ferguson here at Canadian Academy.

by GraphPad.com

iology by the Journal of Cell Biology (pdf well worth reading) A Creative

9 10 mean stdev 95% CI

3.0 4.0 2.5 1.08 0.67

1.0 2.0 1.6 0.52 0.32

The error bars here represent 95%CI.

Manipulate the data and see what happens to:

- The overlap of The error bars


t-test resources

Links:

Front Page

Notice that the

population is labe

i-Biology.n
http://i-biology.net/http://i-biology.net/


7/73

- The value of P (t-test)

H0 = "There is no significant difference between population A and B"

P = 0.029

If P < 0.05 then REJECT H0

There is a significant difference.

If P > 0.05 then ACCEPT the H0

There is no significant difference.


8/73

sd 5% confidence

2.3 1.4

0.4 0.3

setting up error bars.

significant difference.

ommons work by Stephen Taylor

sample size of each

led on th graph (n=10)

et

"Comparing Means"


9/73

Comparing two population means.

repeat 1 2 3 4 5 6 7 8 9 10

Population A

Population B

This graph is set up for you to play with.

Put some sets of raw data in the table.

See what happens to the means.

What happens to the sd?

The error bars represent 95% confidence.

Are your data significantly different?

The t-test can be used as a significance test.

We set a confidence limit of 95%.OR: the probability of the difference being

due to chance (P) is 0.05 or less.

Here's how to present it:

T-test:

H0 = There is no significant difference between population A and population B.

df = (total sample size minus 2)

TTEST = ###### Formula =TTEST (PopARawData:PopBRawData, 2, 2)

Analysis If P > 0.05, accept H0 (less than 95% confident that differences are not due to ch

If P < 0.05, reject H0 (more than 95% confident that differences are not due to c

I reject / accept the null hypothesis.

Conclusion: There is / is not a significant difference between population A and population B.

Can we tell how significant the difference is from the overlap in the error bars

repeat 1 2 3 4 5 6 7 8 9 10

Population A 1.0 2.0 3.0 4.0 3.0 2.0 1.0 2.0 3.0 4.0Population B 2.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0

The erro

Manipul

- The ov

- The val



Population A

(n= )0.0

0.1

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

1.0

Meanvalueofdependentvariable

(unit,

)

Descriptive title which makes clear

investigation. (Error bars represe

3.0

3.5

variable




10/73

H0 = "Th

P =

If P < 0.

There is

If P > 0.

There is



Population A

(n=10)

Population B

(n=10)

0.0

0.5

1.0

1.5

2.0

2.5

Meanvalueofdependen

(unit,

)


11/73

Standard deviation (sd)is used in descriptive statistics.

mean stdev 95% confidence It is a measure of the spread (variability) of most of the data.

#DIV/0! ###### #DIV/0! 34% of data falls above (1sd) and 34% below (1sd) the mean.

#DIV/0! ###### #DIV/0! Therefore 68% of all data falls within 1 sd of the mean.

95% Confidence (95% CI): confidence in the m (link)

We can plot this 95% confidence on error bars on graphs.

If error bars do not overlap, the sets of data are likely to be significant

When setting 95% confidence:

Select =CONFIDENCE.NORM as a function

Alpha is 0.05 (95% confidence)

STDEV select the STDEV cell

Size = number of samples for that value

Quick guide to setting up error bars in Excel:Click on the data points, then Chart Layout, Error bars

Select Error Bars Options, Custom, Select Value

For positive error bar, highlight all '95% confidence' cells.

Do the same for the negative error bar.

Delete horizontal error bars if they are produced.

Click here

to find out more about the t-test.

ance) A Creative Commons work by Stephen Taylor

hance)

Thanks to Dave Ferguson for help on this one!

mean stdev 95% CI

2.5 1.08 0.671.6 0.52 0.32

r bars here represent 95%CI.

ate the data and see what happens to:

erlap of The error bars

lue of P (t-test)

Links:

Front Pagei-Biology.net

"T-test"

Population B

(n= )

the purpose of the

t 95% confidence)


12/73

ere is no significant difference between population A and B"

0.029

5 then REJECT H0

a significant difference.

5 then ACCEPT the H0

no significant difference.


13/73

ly different.


14/73

The t-testThe t-test tests the significance of the difference between two means.

You need to know how to apply the t-test in two ways.

1. By using a calculated value of t and comparing it to a critical value on the t-table (exam).

2. By using Excel on your dataset (for lab reports)

repeat 1 2 3 4 5 6 7 8 9 10 mean stdev

Population A 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 9.0 5.4 2.88

Population B 2.0 2.0 2.0 2.0 3.0 4.0 5.0 5.0 5.0 5.0 3.5 1.43

Always start by stating the null hypothesis. H0 = "There is no significant difference between population A

This is always the same.

Now test it.

Quick Excel Method t-Table method (for exa

Use the formula "=TTEST(array1, array2, tails, type)" 18

tails = 2, type = 2 P= 0.05

P is calculated directly. 2.10

P= 0.08

92.22 % Calculated value of t= 1.87

(From

State the Conclusion:

IfP < 0.05 then REJECT the null hypothesis. Ift > c.v. then REJECT the null

There is a significant difference between A and B. There is a signifi

IfP > 0.05 then ACCEPT the null hypothesis. Ift < c.v. then ACCEPT the null

There is no significant difference between A and B. There is no signi

A Creative Commons work by Stephen Tay


Population A 1.0 2.0 3.0 4.0 3.0 2.0 1.0 2.0 3.0 4.0 2.5 1.08

Population B 2.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0 1.6 0.52

The error bars here rep

Manipulate the data an

- The overlap of The err

- The value of P (t-test)

H0 = "There is no signifi

P = ####



Degrees of freedom =

Critical value (c.v.) of t =

http://www.graphpad.c

Confidence=

Links: Front Page i-Biology.net

Conditions

- Two popu

- At least fi

In Biology,

(P < 0.05) l

Population A

(n=10)

Population B1.5

2.0

2.5

3.0

3.5

ofdependentvariable

(unit,

)


http://www.graphpad.com/quickcalcs/ttest1.cfmhttp://i-biology.net/http://i-biology.net/http://www.graphpad.com/quickcalcs/ttest1.cfm


15/73

If P < 0.05 then REJECT

There is a significant dif

If P > 0.05 then ACCEPT

There is no significant d



0.0

0.5

1.0

.

Meanvalue


16/73

90% 95% 98%

P 0.1 0.05 0.02

Increasing significance

DF 1 6.31 12.71 31.82

22.92 4.30 6.97

3 2.35 3.18 4.54

4 2.13 2.78 3.75

95% confidence 5 2.02 2.57 3.37

1.78 6 1.94 2.45 3.14

0.89 7 1.90 2.37 3.00

8 1.86 2.31 2.90

nd B" 9 1.83 2.26 2.82

10 1.81 2.23 2.76

11 1.80 2.20 2.72

mple data) 12 1.78 2.18 2.68

(total sample size minus 2) 13 1.77 2.16 2.65

14 1.76 2.15 2.63

15 1.75 2.13 2.60

16 1.75 2.12 2.58

(this is given to you in exams) 17 1.74 2.11 2.57

) 18 1.73 2.10 2.55

19 1.73 2.09 2.54

20 1.73 2.09 2.53

ypothesis. 21 1.72 2.08 2.52

cant difference between A and B. 22 1.72 2.07 2.51

23 1.71 2.07 2.50hypothesis. 24 1.71 2.06 2.49

ficant difference between A and B. 25 1.71 2.06 2.4926 1.71 2.06 2.48

lor There's more 27 1.70 2.05 2.47

28 1.70 2.05 2.47

29 1.70 2.05 2.46

30 1.70 2.04 2.46

95% CI 31 1.70 2.04 2.45

0.67 32 1.69 2.04 2.45

0.32 33 1.69 2.04 2.45

34 1.69 2.03 2.44

resent 95%CI. 35 1.69 2.03 2.44

d see what happens to: 36 1.69 2.03 2.43

or bars 37 1.69 2.03 2.43

38 1.69 2.02 2.43

39 1.69 2.02 2.43

ant difference between population A and B" 40 1.68 2.02 2.42

42 1.68 2.02 2.42

44 1.68 2.02 2.41

46 1.68 2.01 2.41

(n-2)

Confidence

om/quickcalcs/ttest1.cfm

for using the t-test:

lation means to compare

ve data points in each population

we usually work at the 95% confidence

level. This means that any differences

Notice that as P decreases,

Click here for some practice


17/73

H0 48 1.68 2.01 2.41

ference. 50 1.68 2.01 2.40

60 1.67 2.00 2.39

the H0 70 1.67 1.99 2.38

ifference. 80 1.66 1.99 2.37

90 1.66 1.99 2.37100 1.66 1.98 2.36

120 1.66 1.98 2.36

150 1.66 1.98 2.35

200 1.65 1.97 2.35

300 1.65 1.97 2.34

500 1.65 1.97 2.33

1.65 1.96 2.33

If your degrees of freedom lie

between values (e.g. 55 on this

table), then use the critical value of

the lower number (in this case 50).


18/73

99%

0.01

63.66

9.93

5.84

4.60

4.03

3.71

3.50

3.36

3.25

3.17

3.11

3.06

3.01

2.98

2.95

2.92

2.90

2.88

2.86

2.85

2.83

2.82

2.81

2.80

2.792.78

2.77

2.76

2.76

2.75

2.74

2.74

2.73

2.73

2.72

2.72

2.72

2.71

2.71

2.70

2.70

2.69

2.69


19/73

2.68

2.68

2.66

2.65

2.64

2.632.63

2.62

2.61

2.60

2.59

2.59

2.58


20/73

Practice with the t-testAlways start by stating the null hypothesis. H0 = "There is no significant difference betwee

1. Total sample size = 24. t = 2.33.


Ift > c.v. then REJECT the null hypothesis. Conclusion:

There is a significant difference between A and B. t c.v. so I the null

There a significant difference bet

Ift < c.v. then ACCEPT the null hypothesis.

There is no significant difference between A and B.

2. An ecologist measures 23 leaves on the East side of a hedge and 32 on the West. The me


Conclusion:

t c.v. so I the null hypothesis.

There a significant difference between the population means.

3. Rugby squad A has 22 players with a mean sprint time of 11.5s. Squad B has 20 players,


Conclusion:

t c.v. so I the null hypothesis.

There a significant difference between the population means.


Population A 1.0 2.0 3.0 4.0 3.0 2.0 1.0 2.0 3.0 4.0 2.5 1.08

Population B 2.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0 1.6 0.52

The error bars here re

Manipulate the data a

- The overlap of The e

- The value of P (t-test

H0 = "There is no signi

P = ###

If P < 0.05 then REJEC


Degrees of freedom = Critical value (c.v.) of t =



Population A

(n=10)

Population B

(n=10)1.5

2.0

2.5

3.0

3.5

lueofdependentvariable

(unit,

)




21/73

There is a significant d

If P > 0.05 then ACCEP

There is no significant



0.0

0.5

1.0

Meanv


22/73

90% 95% 98%

n population A and B" P 0.1 0.05 0.02

Increasing significance

DF 1 6.31 12.71 31.82

22.92 4.30 6.97

3 2.35 3.18 4.54

4 2.13 2.78 3.75

5 2.02 2.57 3.37

l hypothesis. 6 1.94 2.45 3.14

een the population means. 7 1.90 2.37 3.00

8 1.86 2.31 2.90

9 1.83 2.26 2.82

10 1.81 2.23 2.76

ns are compared. "t" is calculated as 1.97. 11 1.80 2.20 2.72

12 1.78 2.18 2.68

131.77 2.16 2.65

14 1.76 2.15 2.63

15 1.75 2.13 2.60

16 1.75 2.12 2.58

17 1.74 2.11 2.57

18 1.73 2.10 2.55

ean time 9.3s. "t" is calculated as 2.10. 19 1.73 2.09 2.54

20 1.73 2.09 2.53

21 1.72 2.08 2.52

22 1.72 2.07 2.51

23 1.71 2.07 2.50

24 1.71 2.06 2.49A Creative Commons work by Stephen Taylor 25 1.71 2.06 2.49

26 1.71 2.06 2.48

27 1.70 2.05 2.47

28 1.70 2.05 2.47

95% CI 29 1.70 2.05 2.46

0.67 30 1.70 2.04 2.46

0.32 31 1.70 2.04 2.45

32 1.69 2.04 2.45

present 95%CI. 33 1.69 2.04 2.45

nd see what happens to: 34 1.69 2.03 2.44

ror bars 35 1.69 2.03 2.44

) 36 1.69 2.03 2.4337 1.69 2.03 2.43

icant difference between population A and B" 38 1.69 2.02 2.43

39 1.69 2.02 2.43

40 1.68 2.02 2.42

42 1.68 2.02 2.42

H0 44 1.68 2.02 2.41

Confidence

(n-2)

Links:

Front Page

i-Biology.net


23/73

ifference. 46 1.68 2.01 2.41

48 1.68 2.01 2.41

T the H0 50 1.68 2.01 2.40

difference. 60 1.67 2.00 2.39

70 1.67 1.99 2.38

801.66 1.99 2.37

90 1.66 1.99 2.37

100 1.66 1.98 2.36

120 1.66 1.98 2.36

150 1.66 1.98 2.35

200 1.65 1.97 2.35

300 1.65 1.97 2.34

500 1.65 1.97 2.33

1.65 1.96 2.33

If your degrees of freedom lie

between values (e.g. 55 on this

table), then use the critical value of

the lower number (in this case 50).


24/73

99%

0.01

63.66

9.93

5.84

4.60

4.03

3.71

3.50

3.36

3.25

3.17

3.11

3.06

3.01

2.98

2.95

2.92

2.90

2.88

2.86

2.85

2.83

2.82

2.81

2.80

2.79

2.78

2.77

2.76

2.76

2.75

2.74

2.74

2.73

2.73

2.72

2.722.72

2.71

2.71

2.70

2.70

2.69


25/73

2.69

2.68

2.68

2.66

2.65

2.64

2.63

2.63

2.62

2.61

2.60

2.59

2.59

2.58


26/73

Data Processing and Presentation

Raw data table of tissue sample masses for populations A and B

repeat 1 2 3 4 5 6 7 8 9 10 mean

Population A 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 1.1 1.5

Population B 2.3 2.3 2.4 2.0 2.3 4.3 2.2 2.2 2.0 3.1 2.5

Associated qualitatvie data:

Record observations with the raw data. You cannot get above 0 for DCP aspect 1 without this.

These observations could be referred to in the conclusion or evaluation.

Mass of tissue samples (g, 0.1g)

Population A

(n= )

Population B

(n= )

0.0

0.5

1.0

1.5

2.0

2.5

3.0

3.5

Meanmassoftissuesamples(g,

0.1g

)

Comparing the mean tissue sample masses of Population A and

Population B (Error bars represent 95% confidence)


27/73

Raw data

Give the tables a title.

Always remember units and uncertainties.

stdev 95% confidence Mean should not got to more d.p. than the precision of the recording.

0.29 0.18 Make sure that all raw data and the means have consistent d.p.

0.70 0.43 This includes zeroes. (Format cell, number, dp)

STDEV and 95% confidence can have one extra d.p.

Do not allow tables to break across pages.

Processing data:

Explain what calculations were used and why.

If you used Excel, explain what functions were selected and why.

Give worked examples of any calculations you carried out yourself.

Where data sets are large, you will need to present processed data as a separate table.

Think about units and uncertainties of processed data carefully.

Presenting Processed DataTitles must be descriptive of the experiment.

You must state what the error bars represent.

Do not plot bar charts.

Trend lines, if used, should be plotted by you.

Error bars need to be for each data set. Include sample size where appropriate (n= )

Label axes clearly, with units and uncertainties.

Make good use of space.

Do not clutter the graph with unnecessary colours, lines or shading.

Ask yourself the question:

"Does my table or graph clearly and accurately represent the data?"

A Creative Commons work by Steph

Links:



28/73

en Taylor


29/73

Comparing two population means under different conditionsIn this example, lichen and moss populations were compared on the North and South sides of so

1st

quadrat

2nd

quadrat

3rd

quadrat

1st

quadrat

2nd

quadrat

3rd

quadrat

Moss 4 20 10 56 45 65

Lichen 100 84 100 12 32 12Moss 5 6 23 76 76 65

Lichen 87 65 54 23 12 23

Moss 12 13 14 76 87 65

Lichen 3 4 5 12 10 9

Moss 76 65 54 67 54 23

Lichen 3 6 5 3 6 5

Moss 76 87 78 87 76 67

Lichen 2 4 7 12 8 6

Sample sizes: 15 per side per type

Interpret the data and the graphs.

Which data are more variable? How do you know?

Which sets of data are most likely to be significantly different?

A Creative Commons work by Stephen Taylor

% Coverage of lichens and

mosses on trees (4%)

Northern direction Southern direction

Tree 1

Tree 2

Links:

Front Page

i-Biology.net

Tree 3

Tree 4

Tree 5


30/73

e trees.

Processed Data

Mean Standard deviation 95% confidenceMosses 36.2 32.2 16.3

Lichen 33.1 40.4 20.4

Mosses 65.7 22.9 11.6

Lichen 11.6 8.2 4.2

% Coverage on trees (4%)

South Side

North Side

Moss, North

36.2 Lichen, North

33.1

Moss, South

65.7

Lichen, S

0.0

10.0

20.0

30.0

40.0

50.0

60.0

70.0

80.0

90.0

Mean%Coverageontree

s(

4%)

Comparing % coverage of moss and lichen populations on the Nort

South aspects of trees in Bandung, West Java. (Error bars represent

confidence)


31/73

uth

11.6

and

95%


32/73

A results table for population comparisons where membe

1 2 3 4 5 mean stdev 95% conf.

A1 ###### #DIV/0!

A2 ###### #DIV/0!

A3 ###### #DIV/0!

A4 ###### #DIV/0!

A5 ###### #DIV/0!

mean ###### ###### ###### ###### ######

stdev ###### ###### ###### ###### ######

95% confidence ###### ###### ###### ###### ######

population statistics:

mean = ###### stdev = ###### 95% confidence #DIV/0!

T-test:

H0 = There is no significant difference between population A and population B.

df =

TTEST =

Analysis If P>0.05, accept H0 (less than 95% confident that differences are not du

If P


33/73

s of each population are repeating trials

1 2 3 4 5 mean stdev 95% conf.

B1 ###### #DIV/0! #DIV/0!

B2 ###### #DIV/0! #DIV/0!

B3 ###### #DIV/0! #DIV/0!

B4 ###### #DIV/0! #DIV/0!

B5 ###### #DIV/0! #DIV/0!

mean ###### ###### ###### ###### ######

stdev ###### ###### ###### ###### ######

95% confidence ###### ###### ###### ###### ######

population statistics:

mean = ###### stdev = ###### 95% confidence= #DIV/0!

e to chance)

ue to chance)

B. A Creative Commons work by Ste

i-Biology.net

Population BDV unit ( smallest division)

Links:

Front Page


34/73

hen Taylor


35/73

CorrelationsThese examples outline some different types of correlation.

See what happens to the correlation coefficient, the scatter plots and the best-fit lines when you change some raw data points.

Variable A 3.0 4.0 5.0 5.0 5.0 6.0 6.0 8.0 8.0 9.0

Variable B 2.7 2.8 2.8 2.9 2.9 2.9 3 3.1 3.4 3.6

Variable A 3.0 4.0 5.0 5.0 5.0 6.0 6.0 8.0 8.0 9.0

Variable B 5 2 7 9 6 7 8 3 2 10

Variable A 3.0 4.0 5.0 5.0 5.0 6.0 6.0 8.0 8.0 9.0Variable B 9 8 6 7 4 6 6 5 4 3

Correlation = 0.92

Correlation = -0.84

Correlation = 0.10

0

0.5

1

1.5

2

2.5

3

3.5

4

2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0VariableB(units,uncertainties)

Variable A (units, uncertainties)

0

2

4

6

8

10

12

2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0VariableB(units,unce

rtainties)


4

6

8

10

units,uncertainties)

correlation = 0.92

correlation = 0.10 (no correlation)

correlation = -0.84


36/73

Variable A 3.0 4.0 5.0 5.0 5.0 6.0 6.0 8.0 8.0 9.0

Variable B 9 8 7 7 7 6 6 4 4 3Correlation = -1.00

0

2

2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0VariableB


0

2

4

6

8

10

2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0VariableB

(units,uncertatinies)


correlation = -1.00


37/73

Try it here:

Raw data points: 1 2 3 4 5 6 7 8 9 10 mean sd

Variable A (units, ) #DIV/0! #DIV/0!

Variable B (units, ) #DIV/0! #DIV/0!Remember, variable A and B must align! Mean and s.d. are just descriptive here.

Insert columns before the mean for extra data points.

Links:

More examples down here Front Page

i-Biology.net


0

0.2

0.4

0.6

0.8

1

1.2

0 0.2 0.4 0.6 0.8 1 1.2 1.4 1.6 1.8 2 2.2 2.4 2.6 2.8 3 3.2 3.4 3.6 3.8 4

VariableB(units,)

Variable (units, )

A scatter plot to show the relationship between Variable A and Variable B

(Correlation = )

Remember: Correlation does not imply


38/73


39/73


40/73

Before and AfterA 5 x 5 results table to produce a single scatter or line

IV Value ( (unit)) repeat start end difference % difference ( x%)

1 0 #DIV/0!

2 0 #DIV/0!3 0 #DIV/0!

4 0 #DIV/0!

5 0 #DIV/0!

1 0 #DIV/0!

2 0 #DIV/0!

3 0 #DIV/0!

4 0 #DIV/0!

5 0 #DIV/0!

1 0 #DIV/0!

2 0 #DIV/0!

3 0 #DIV/0!

4 0 #DIV/0!

5 0 #DIV/0!

1 0 #DIV/0!

2 0 #DIV/0!

3 0 #DIV/0!

4 0 #DIV/0!

5 0 #DIV/0!

1 0 #DIV/0!

2 0 #DIV/0!

3 0 #DIV/0!

4 0 #DIV/0!

5 0 #DIV/0!

recorded value ( (unit))

1

2

3

4

5

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

1.0

n%changeofdependentv

ariable

(unitsanduncertainties)

Investigating the effect of changing independent variabl

dependent variable of _____. Error bars represent


41/73

0.0

0.1

0 1 2 3

Mea

Independent variable (units and unce


42/73

mean ( x%) stdev 95% confidence Calculating % differences allows for comparisons b

where starting values may not be consistent.

For example, an increase of mass of 0.1g on a start

1g is much greater than the same increase on a sta

Calculating % difference:

end - start

start

Graph

Links:

Front Page

i-Biology.netA Creative Commons w

Add your own trend line.

Do not use the Excel lines.

Best fit and get between the error bars where poss

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

% difference = x 100

#DIV/0!

on the % change in

5% confidence.


43/73

4 5 6

rtainties)


44/73

etween sets of data

ing mass of

rting mass of 5g.

rk by Stephen Taylor

ible.


45/73

Continuous variable: 5 increments, 5 repeats, changes over tiA 5 x 5 results table for time trials over 5 increments of the IV (raw data)

IV Value ( (unit)) Time ( (unit)) 1 2 3 4 5 Mean STDEV

1 #DIV/0! #DIV/0!

2 #DIV/0! #DIV/0!

3 #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0!

1 #DIV/0! #DIV/0!

2 #DIV/0! #DIV/0!

3 #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0!

1 #DIV/0! #DIV/0!

2 #DIV/0! #DIV/0!3 #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0!

1 #DIV/0! #DIV/0!

2 #DIV/0! #DIV/0!

3 #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0!

1 #DIV/0! #DIV/0!

2 #DIV/0! #DIV/0!

3 #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0!

Repeat Processe

4

5

3

1

2

0.5

0.6

0.7

0.8

0.9

1.0

endentvariable

uncertainties)

Investigating the effect of (IV) on (DV).

(Error bars represent 95% confidence.)


46/73

IV Value ( (unit)) Mean % change sd 95% CI Use axis formatting tools to sort th

1 #DIV/0! #DIV/0! #DIV/0! to fit the data and increments appr

2 #DIV/0! #DIV/0! #DIV/0!

3 #DIV/0! #DIV/0! #DIV/0!

4 #DIV/0! #DIV/0! #DIV/0!

5 #DIV/0! #DIV/0! #DIV/0!

0.0

0.1

0.2

0.3

0.4

0 1 2 3 4 5

Meanofde

(units

Independent Variable (units uncertainties)


47/73

e

This is the standard "5 x 5" investigation that can generate "sufficient, relevant data"

95% confidence As so much raw data is generated, processed data should be separated.

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0! Links:

#DIV/0! Front Pa

#DIV/0! i-Biology.

#DIV/0! Do more! A Creative

With such a rich data-set, we can 'lift out' values to see more trends.

Example: see the 'before-after' results for each increment of the dependent variable.

IV Value ( (unit)) Time ( (unit)) 1 2 3 41 0.0 0.0 0.0 0.0

5 0.0 0.0 0.0 0.0

Difference 0.0 0.0 0.0 0.0

% Change #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Mean % change #DIV/0! sd = #DIV/0! 95% CI

1 0.0 0.0 0.0 0.0

5 0.0 0.0 0.0 0.0

Difference 0.0 0.0 0.0 0.0

Data

Link to a completed example

Repeat

1

2

0.0

0.1

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

1.0

0 1 2 3 4

Meano

fdependentvariable

(unitsu

ncertainties)

Time (units uncertainties)

Investigating the effect of (IV) on (DV) over ti(Error bars represent 95% confidence.)

1 2 3 4 5


48/73



1 0.0 0.0 0.0 0.0

5 0.0 0.0 0.0 0.0

Difference 0.0 0.0 0.0 0.0


Mean % change #DIV/0! sd = #DIV/0! 95% CI1 0.0 0.0 0.0 0.0

5 0.0 0.0 0.0 0.0

Difference 0.0 0.0 0.0 0.0


m out Mean % change #DIV/0! sd = #DIV/0! 95% CI

opriately. 1 0.0 0.0 0.0 0.0

5 0.0 0.0 0.0 0.0

Difference 0.0 0.0 0.0 0.0



4

5

3

6


49/73

e

net

Commons work by Stephen Taylor

50.0

0.0

0.0

#DIV/0!

#DIV/0!

0.0

0.0

0.0

5 6

me.


50/73

#DIV/0!

#DIV/0!

0.0

0.0

0.0

#DIV/0!

#DIV/0!0.0

0.0

0.0

#DIV/0!

#DIV/0!

0.0

0.0

0.0

#DIV/0!

#DIV/0!


51/73

Continuous variable: 5 increments, 5 repeats, changes overA 5 x 5 results table for time trials over 5 increments of the IV (raw data)

CO2 production ( 0.5ml)

Temp ( 0.5oC) Time ( 1 sec) 1 2 3 4 5 Mean STDEV

0 0.0 0.0 0.0 0.0 0.0 0.0 0.00

60 .1. 0.1 0.1 0.2 0.0 0.1 0.08

120 0.1 0.2 0.1 0.2 0.0 0.1 0.08

180 0.1 0.2 0.1 0.2 0.1 0.1 0.05

240 0.1 0.3 0.1 0.3 0.1 0.2 0.11

0 0.0 0.0 0.0 0.0 0.0 0.0 0.00

60 0.3 0.4 0.3 0.2 0.3 0.3 0.07

120 0.4 0.4 0.4 0.2 0.4 0.4 0.09

180 0.6 0.5 0.4 0.3 0.5 0.5 0.11

240 0.7 0.6 0.6 0.3 0.6 0.6 0.15

0 0.0 0.0 0.0 0.0 0.0 0.0 0.00

60 0.5 0.6 0.5 0.3 0.5 0.5 0.11120 0.8 0.9 0.8 0.6 0.5 0.7 0.16

180 1.0 1.0 1.1 0.8 0.8 0.9 0.13

240 1.2 1.2 1.5 1.3 1.3 1.3 0.12

0 0.0 0.0 0.0 0.0 0.0 0.0 0.00

60 0.8 0.9 0.8 0.8 0.8 0.8 0.04

120 1.2 1.6 0.9 0.9 1.4 1.2 0.31

180 1.5 1.9 1.5 1.7 1.9 1.7 0.20

240 1.8 2.0 2.3 1.8 2.5 2.1 0.31

0 0.0 0.0 0.0 0.0 0.0 0.0 0.00

60 0.1 0.3 0.2 0.1 0.1 0.2 0.09

120 0.1 0.3 0.3 0.2 0.2 0.2 0.08

180 0.2 0.3 0.4 0.2 0.2 0.3 0.09

240 0.3 0.3 0.5 0.3 0.2 0.3 0.11

30oC

40oC

50oC

Repeat Processe

10oC

20oC


52/73

time This is the standard "5 x 5" investigation that can generate "sufficient, rele

95% confidence

#NUM!

0.07

0.07

0.05

0.10

#NUM!

0.06

0.08

0.10

0.13

#NUM!

0.100.14

0.12

0.11

#NUM!

0.04

0.27

0.18

0.27

#NUM!

0.08 You need to edit axes and labels. Links:

0.07 Best fit should be done yourself. Front Page

0.08 In this case, I chose polynomials which fit. i-Biology.net

0.10 A Creative Common

Data

0.0

0.5

1.0

1.5

2.0

2.5

0 60 120 180

Mean

ofdependentvariable

(unitsu

ncertainties)

Independent Variable (units uncertainties)

Investigating the effect of temperature on rate of photo

ofElodea, by measuring release of CO2 over tim

(Error bars represent 95% confidence.)

10oC 20oC 30oC 40oC 50oC


53/73

ant data"

work by Stephen Taylor

240

ynthesis

.


54/73

How does glucose concentration affect population growth ofSacch

Raw data: Cell counts from the hemacytometer

cells squares cells squares cells

0 10 16 9 16 9

24

48

72

96

0 16 16

24

48

7296

0 16 16

24

48

72

96

0 16 16

24

48

7296

0 16 16

24

48

72

96

Processed Data: Population estimates

1 2 3 4 5

0 400 360 360 400 480

24 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!




Glucose concentration

( 1gl-1

)

Population ( x 103

cells cm-3

)Time (

4h)

15

20

Time (

4h)

Glucose concentration

( 1gl-1)

Population 1 Population 2 Popul

0

5

Raw cel

0

10


55/73

0 0 0 0 0 0





0 0 0 0 0 0





0 0 0 0 0 0





0 0 0 0 0 0





5

10

15

20


56/73

aromyces cerevisiae ?

squares cells squares cells squares The red values

16 10 16 12 16 1 A dilution fact

1 If cells become

1

1 Associated Qu

1 Samples for 15

16 16 16 1 The dilution fa

1

1

11

16 16 16 1

1

1

1

1

16 16 16 1

1 Links:

1 Front P

1 i-Biolo2 A Creati

16 16 16 1

1

1

1

2

Processed Data: Graphing

Further ProcessingTurn this blank chart into a scatter plot ofme

Mean STDEV 95% CI Plot 95% CI as the error bars and for each seri

400 49.0 42.9 Include: a descriptive title, units an uncertaint

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

http://

lation 3 Population 4 Population 5

Link to data:

Dilution

Factor

"1 in ___"

l counts (1)
http://i-biology.net/http://wp.me/P7lr1-kNhttp://i-biology.net/


57/73

16 0.0 95% CI

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

16 0.0 95% CI

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

16 0.0 95% CI

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

16 0.0 95% CI

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0!


58/73

have been entered from the starting populations.

r of "none" is 1 in 1

too clustered to count, we need to dilute and factor.

alitative Data (observations)

gl-1

and 20gl-1

had to be diluted on day 5 as they were too clustered to count.

tor was 1 in 2 (5ml sample added to 5ml water, then re-sampled).

age

y.net ve Commons work by Stephen Taylor

an population over time, with 5 curves (concentrations). Model:

es, select an appropriate best-fit curve.

ies on the axes, clear points and lines and make good use of the space.

p.me/P7lr1-kN

5 x 5 example


59/73


60/73

Chi-Squared Test Calculator: Monohybrid Cr

Null hypothesis: there is no significant difference between observed and expected

Description Observed (O) Expected (E)

Phenotype 1 #DIV/0! #DIV/0!

Phenotype 2* #DIV/0! #DIV/0!

Sum: 0 #DIV/0! 0 #DIV/0!

*clear cells if not needed#Write as a an integer, e.g. ( 3, 1) If

2< critical value, accept the

If2

> critical value, reject the n

Conclusion: I do/do do not accept the null hypothesis. My results do/ d

Sample size (N) =

predicted

ratio#

ratio as a

decimal

Collected data

These totals must equal N


61/73

ss (not multiple alleles)

esults .

(O - E) (O - E)2 D.F. $

0.1

(90%)

0.05

(95%)

0.01

(99%)

#DIV/0! #DIV/0! #DIV/0! 1 2.70554 3.84146 6.6349

#DIV/0! #DIV/0! #DIV/0! 2 4.60517 5.99146 9.21034

#DIV/0! $Degrees of freedom:number of phenotypes minus 1

null hypothesis

ull hypothesis

not fit the predicted ratio.

Links:


p(certainty)

Total (2

) =

Front Page

i-Biology.net

Calculating 2

(O - E)2

E

Critical Values table


62/73

Chi-Squared Test Calculator: Dihybrid Crosse







Sum: 0 #DIV/0! 0 #DIV/0!

*clear cells if not needed

#Write as a an integer, e.g. (9, 3, 3, 1) If


If2



Sample size (N) =

predicted

ratio#

ratio as a

decimal

Collected data



63/73

s & Linked Genes

esults .

(O - E) (O - E)2 D.F. $

0.1

(90%)

0.05

(95%)

0.01

(99%)

#DIV/0! #DIV/0! #DIV/0! 1 2.70554 3.84146 6.6349

#DIV/0! #DIV/0! #DIV/0! 2 4.60517 5.99146 9.21034

#DIV/0! #DIV/0! #DIV/0! 3 6.25139 7.81473 11.34487

#DIV/0! #DIV/0! #DIV/0! 4 7.77944 9.48773 13.2767

#DIV/0! $Degrees of freedom:

number of phenotypes minus 1

null hypothesis

ull hypothesis


Links:


p(certainty)

Total (

2

) =


Calculating 2

(O - E)2

E



64/73

Chi-Squared Test Calculator: Codominant (M







Sum: 0 #DIV/0! 0 #DIV/0!




If2



Sample size (N) =

predicted

ratio#

ratio as a

decimal

Collected data



65/73

onohybrid)

esults .

(O - E) (O - E)2 D.F. $

0.1

(90%)

0.05

(95%)

0.01

(99%)

#DIV/0! #DIV/0! #DIV/0! 1 2.70554 3.84146 6.6349

#DIV/0! #DIV/0! #DIV/0! 2 4.60517 5.99146 9.21034

#DIV/0! #DIV/0! #DIV/0! 3 6.25139 7.81473 11.34487

#DIV/0! #DIV/0! #DIV/0! 4 7.77944 9.48773 13.2767

#DIV/0! $Degrees of freedom:

number of phenotypes minus 1

null hypothesis

ull hypothesis


Links:



p(certainty)

Total (

2

) =

Calculating 2

(O - E)2

E



66/73

Chi-Squared Test Calculator: Polygenic or TriNull hypothesis: there is no significant difference between observed and expected








Sum: 0 #DIV/0! 0 #DIV/0!




If2



Sample size (N) =

predicted

ratio#

ratio as a

decimal

Collected data



67/73

hybrid Crossesesults .

(O - E) (O - E)2 D.F. $

0.1

(90%)

0.05

(95%)

0.01

(99%)

#DIV/0! #DIV/0! #DIV/0! 1 2.70554 3.84146 6.6349

#DIV/0! #DIV/0! #DIV/0! 2 4.60517 5.99146 9.21034

#DIV/0! #DIV/0! #DIV/0! 3 6.25139 7.81473 11.34487

#DIV/0! #DIV/0! #DIV/0! 4 7.77944 9.48773 13.2767

#DIV/0! #DIV/0! #DIV/0! 5 9.23636 11.0705 15.08627

#DIV/0! #DIV/0! #DIV/0! 6 10.6446 12.59159 16.81189

#DIV/0! $Degrees of freedom:number of phenotypes minus 1

null hypothesis

ull hypothesis


Links:



p(certainty)

Total (2

) =

Calculating 2

(O - E)2

E



68/73

Percentage Difference vs Percentage Change When is the d

A = 0.50 If A is simply bein

B = 0.75 If A has become

% Difference: Simple Comparisons % Change:

difference

A or B

No negative values - the magnitude of the difference only is considered. Negative values a

Therefore two answers are acceptable.

0.75 - 0.50

0.50

OR

0.75 - 0.500.75

Change =riginal value:

Final value:% Change Calculator

1

2

Consider these two data points.

%

%

Click on the white cells to see the formula used

x 100 = 33

% Difference = x 100 % Change =

50x 100 = % Change =

% Change =


69/73

istinction between these important?

compared to B, we use one method.

(changed over time), we use another.

rder is important

(Final - original)

Original Value

re possible. Magnitude and 'direction' of the change are shown.

0.75 - 0.50

0.5

What if A = 0.75 ?and B = 0.50 ?

0.50 - 0.75

0.5

Links:

You need to adjust the d.p. to the Front Page

same degree of precision as the measurements. i-Biology.net

A Creative Commons work by

100.00 %

x 100

50 % (increase)

x100 = -33.3 % (decrease)

x100 =


70/73

Stephen Taylor


71/73

Calculating Magnifications

You should be able to:

1. Calculate the linear magnification of an image when given a s

ruler length 2.5cm 25,000mscale bar 1m 1m

get to the same units

2. Calculate the actual size of an object when given the linear m"An image is magnified 5,000x. Calculate the actual size of an organelle meas

ruler length 2mm

magnification 5000

horrible notation

3. Calculate the actual size of an object when given a scale bar."A scale bar of 10m is 50mm on an image. Calculate the actual size of an or

scale bar length 50mm

object length 23mmx scaleActual size = = x 10m

=Magnification=

Actual size = = =.0004mm=

= =


72/73

ale bar.

gnification.ured as 2mm on the image."

4 x 10-4

mm

0.4m nm m mm

400nm nm 1 1 x 10-3

1 x 10-6

nicer notations m 1 x 103 1 1 x 10-3

mm 1 x 106

1 x 103 1

m 1 x 109

1 x 106

1 x 103

km 1 x 1012

1 x 109

1 x 106

anelle measured as 23mm on the image."

Pick the most appropriate unit and notation to present

Proper scientific notation should have one integer bef

Links:


A Creative Co

= 21.7m

is worth _____

These relationships are fa

Oneofthese

5,000 x magnification 1


73/73

m km cm

1 x 10-9

1 x 10-12

1 x 10-7

1 x 10-6 1 x 10-9 1 x 10 -4

1 x 10-3

1 x 10-6

1 x 10-1

1 1 x 10-3

1 x 102

1 x 103 1 1 x 10

5

your data.

re the decimal place.

mons work by Stephen Taylor

_ of one of these:

tors of 1000

i-biology excel statbook

Documents