Exercise 10.2.a.

These notes are concerned with the Kruskal-Wallis test (Section 10.2). The notes give the critical values for the Kruskal-Wallis test under various scenarios.

• Introduction
• Approximate values for sample sizes >=5
• Exact values for equal sample sizes
  • 3 groups
  • 4 groups
  • 5 groups
  • 6 groups
• Exact values for unequal sample sizes
  • Max group size 1-5
  • Max group size 6-9
  • Max group size 10-17

The Kruskal-Wallis test is appropriate when you have non-parametric data and one predictor variable (Section 10.2). It is analogous to a one-way ANOVA but uses ranks of items in various groups to determine the likely significance.

If there are at least 5 replicates in each group, the critical values are close to the Chi-Squared distribution. There are exact critical values computed when you have equal group sizes. There are also exact critical values for situation where you have un-equal group sizes.

When you have at least 5 observations in each group the Kruskal-Wallis critical value is approximately the same as Chi Squared. You need to determine the degrees of freedom, which are the number of groups minus 1. You can reject the null hypothesis if your calculated value of H is bigger than the tabulated value.

Critical values for H where all samples have at least 5 replicates.

If you have the same number of replicates for each group you can use a table of exact values, instead of the Chi Squared approximation. There are separate tables for different numbers of groups. Reject the null hypothesis if your calculated value of H is equal or greater than the tabulated value for the appropriate sample size.

Exact critical values for H for four groups of equal size.

Exact critical values for H for five groups of equal size.

Exact critical values for H for six groups of equal size.

When you have different sample sizes you can look up exact critical values using the following table(s).

The sample sizes are given in the first column, so 222110 is equivalent to 5 groups with replicates of 2, 2, 2, 1 and 1. Reject the null hypothesis if your calculated value of H is equal or greater than the tabulated value.

Critical values of H for unequal group sizes (max replicates 5).

The sample sizes are given in the first column, so 611000 is equivalent to 3 groups with replicates of 6, 1 and 1. Reject the null hypothesis if your calculated value of H is equal or greater than the tabulated value.

Critical values of H for unequal group sizes (replicates 6 to 9).

The sample sizes are given in the first column (the first two digits are values 10-17), so 1011000 is equivalent to 3 groups with replicates of 10, 1 and 1. Reject the null hypothesis if your calculated value of H is equal or greater than the tabulated value.

Critical values of H for unequal group sizes (replicates 10 to 17).

Exercise 10.1.5.

This exercise is concerned with analysis of variance (ANOVA) in Chapter 10. In particular with the situation when you have two predictor variables, that is two-way ANOVA.

• Introduction
  • The exercise data
• Calculate column sums of squares
• Calculate row sums of squares
• Calculate error sums of squares (within groups)
• Calculate interaction sums of squares
• Compute total sums of squares and degrees of freedom
• Make the final ANOVA table
• Graphing the result

Excel can carry out the necessary calculations to conduct ANOVA and has several useful functions that can help you:

• FDIST – calculates an exact p-value for a given F-value (and degrees of freedom).
• VAR – computes the variance of a series of values.
• COUNT – counts the number of items, useful for degrees of freedom.
• AVERAGE – calculates the mean.

However, Excel is most suitable for one-way ANOVA, where you have a single predictor variable. When you have two predictor variables two-way ANOVA is possible, but can be tricky to arrange.

In order to carry out the calculations you need to have your data arranged in a particular layout, let’s call it sample layout or “on the ground” layout. This is not generally a good layout to record your results but it is the only way you can proceed sensibly using Excel. In this exercise you’ll see how to set out your data and have a go at the necessary calculations to perform a two-way ANOVA.

The data for this exercise are available as a file: Two Way online.xlsx. There are two worksheets, one with the bare data and one completed version so you can check your work.

You can use the Analysis ToolPak to carry out the computations for you but you’ll still need to arrange the data in a particular manner. The Analysis ToolPak is not “active” by default so you may need to go to the options/settings and look for Add-Ins.

The data you’ll use for this exercise are in the file Two Way online.xlsx and are presented in the following table:

Exercise data for two-way anova.

These data represent the growth of two different plant species under three different watering regimes. The first column shows the levels of the Water variable, this is one of the predictor variables and you can see that there are three levels: Lo, Mid and Hi. The next two columns show the growth results for two plant species, labelled vulgaris and sativa. These two columns form the second predictor variable (we’ll call that Plant, which seems suitable).

This layout is the only way that Excel can deal with the values but it is not necessarily the most useful general layout for your data. The scientific recording layout is more powerful and flexible.

The other thing to note is that there are an equal number of items (replicates) in each “block”. Here there are only 3 observations per block. This replication balance is important; the more unbalanced your situation is the more “unreliable” the result is. In fact, if you use the Analysis ToolPak for 2-way ANOVA you must have a completely balanced dataset (or the routine refuses to run).

Start by opening the example datafile: Two Way online.xlsx. Make sure you go to the Data worksheet (the worksheet Completed is there for you to check your results). The data are set out like the previous table, in sample layout. You will need to calculate the various sums of squares and to help you the worksheet has some extra areas highlighted for you.

Start by calculating the column SS, that is the sums of squares for the Plant predictor.

In the formula x represents each column, T is the overall data and n is the number of samples.

1. Click in cell A12 and type a label, Col SS, for the sums of squares of the columns (the Plant predictor variable).
2. In Cell B12 type a formula to calculate the SS for the vulgaris column: =(AVERAGE(B2:B10)-AVERAGE(B2:C10))^2*COUNT(B2:B10). You should get 7.11.
3. In C12 type a similar formula to get the SS for the sativa column: =(AVERAGE(C2:C10)-AVERAGE(B2:C10))^2*COUNT(C2:C10). You should get 7.11.

You should now have the two sums of squares for the columns (the Plant predictor).

The row SS are calculated in a similar manner to the col SS.

1. In cell E3 type a formula to compute the row SS for the Water block Lo: =(AVERAGE(B2:C4)-AVERAGE(B2:C10))^2*COUNT(B2:C4). You should get 880.07.
2. In E6 compute row SS for the Mid block: =(AVERAGE(B5:C7)-AVERAGE(B2:C10))^2*COUNT(B5:C7). You should get 71.19.
3. In E9 compute row SS for the Hi block: =(AVERAGE(B8:C10)-AVERAGE(B2:C10))^2*COUNT(B8:C10). You should get 1451.85.
4. In E12 calculate the overall row SS: =SUM(E2:E10). You should get 2401.11.
5. In F12 type a label, Row SS, to remind you what this value is.

You’ve now got the row and column SS.

The next step is to determine the error sums of squares. You can get this by multiplying the block variance by the number of replicates for each group minus 1. This is essentially a tinkering of the formula for variance:

1. In H3 type a formula to calculate the SS for the Lo Water and vulgaris Plant block: =VAR(B2:B4)*2. You should get 12.67.
2. Repeat the previous step for the rest of the blocks. You’ll need to highlight the appropriate values for each block. Note that the *2 part is the same for all blocks, as there are three replicates for each block.
3. In H12 type a formula to calculate the overall error SS: =SUM(H2:I10). You should get 69.33.
4. In I13 type a label, Error SS, to remind you what the value represents.

You now have the error term for the ANOVA. This is an important value, as you’ll need it to calculate the final F-values.

The final SS component is that for the interactions between the two variables (Water and Plant). To do this you use the means of the various groups and the replication like so:

The formula looks horrendouns but in reality it is more tedious than really hard. The first mean is the mean of a single block. The next Xa, is essentially the column mean. The Xb mean is the “row” mean. The final mean (double overbar) is the overall mean. The n is the number of replicates in each block.

1. In K3 type a formula for the interaction SS for the fisrt block: =(AVERAGE(B2:B4)-AVERAGE(B2:B10)-AVERAGE(B2:C4)+AVERAGE(B2:C10))^2*COUNT(B2:B4). You should get 14.81.
2. In K6: =(AVERAGE(B5:B7)-AVERAGE(B2:B10)-AVERAGE(B5:C7)+AVERAGE(B2:C10))^2*COUNT(B5:B7). Gives 7.26.
3. In K9: =(AVERAGE(B8:B10)-AVERAGE(B2:B10)-AVERAGE(B8:C10)+AVERAGE(B2:C10))^2*COUNT(B8:B10). Gives 42.81.
4. In L3: =(AVERAGE(C2:C4)-AVERAGE(C2:C10)-AVERAGE(B2:C4)+AVERAGE(B2:C10))^2*COUNT(C2:C4). Gives 14.81.
5. In L6: =(AVERAGE(C5:C7)-AVERAGE(C2:C10)-AVERAGE(B5:C7)+AVERAGE(B2:C10))^2*COUNT(C5:C7). Gives 7.26.
6. In L9: =(AVERAGE(C8:C10)-AVERAGE(C2:C10)-AVERAGE(B8:C10)+AVERAGE(B2:C10))^2*COUNT(C8:C10). Gives 42.81.
7. In K12 type a formula to get the total interaction SS: =SUM(K2:L10). You should get 129.78.
8. In L12 type a label, Interact SS, to remind you what the value represents.

Now you have all the sums of squares values you need to complete the ANOVA.

The total sums of squares can be calculated by adding the component SS together. On the other hand, it would be good to check your maths by calculating it from the total variance and df.

You’ll also need the degrees of freedom for the various components before you can construct the final ANOVA table.

1. In A13 type a label, Total SS, for the overall sums of squares.
2. In B13 type a formula to calculate the total SS: =VAR(B2:C10)*(COUNT(B2:C10)-1). You should get 2616.44.
3. In A14 type a label, df, for the degrees of freedom.
4. In B14 type a formula for the column df: =COUNT(B12:C12)-1. The result should be 1.
5. In E14 type a formula for row df: =COUNT(E2:E10)-1. You should get 2.
6. In H14 work out the error df: =C18*C19. You should get 2.
7. In K14 work out the interaction df: =COUNT(B2:C10)-COUNT(K2:L10). You should get 12.

Now you have everything except the total df, which you can place in the final ANOVA table shortly.

You can now construct the final ANOVA table and compute the F-values and significance of the various components. You want your table to end up looking like the following:

Completed anova table for two-way analysis of variance.

1. Start by typing a label, ANOVA, into cell A16.
2. Type the rest of the labels for the ANOVA table as shown above.
3. In the SS column you can place the sums of squares results, which you’ve already computed. So in B18: =E12. In B19: =SUM(B12:C12). and so on.
4. The total SS can be =B13 or a sum of the individual SS components.
5. In the df column you can place the values, which are already computed. The total SS in C22 needs to be determined: =COUNT(B2:C10)-1. You should get 17.
6. The MS are worked out by dividing the SS by the df. For e.g. in D18: =B18/C18.
7. Determine an F-value by dividing the MS for a row by the Error MS. So in E18: =D18/D21. Gives 207.96.
8. Work out an exact p-value for each of your F-values using the FDIST function. You need your F-value, the df for that row and the error df. In F18 type: =FDIST(E18,C18,C21). The result should be 4.9E-10, which is highly significant.
9. In F19: =FDIST(E19,C19,C21).
10. In F20: =FDIST(E20,C20,C21).
11. You can use the FINV function to get a critical value for F. You’ll need a level of significance (0.05), the df for that row and the error df. In G18 type: =FINV(0.05,C18,C21). The result is 3.89.
12. In G19: =FINV(0.05,C19,C21).
13. In G20: =FINV(0.05,C20,C21).

Now you have the final ANOVA table completed. You can see that the interaction term is highly significant (p = 0.0018). The Water treatment is also significant but the Plant variable is not (p = 0.14).

Exercise 10.1.4.

When you have data in the “wrong” layout you need to be able to rearrange them into a more “sensible” layout so that you can unleash the power of R most effectively. The stack() command is a useful tool that can help you achieve this layout.

• Introduction
• Convert a single predictor
  • Remove NA items
  • Re-number row names
• Convert multiple predictors
  • Replace a factor predictor variable

There are two main ways you can layout your data. In sample-format each column is a separate sample, which forms some kind of logical sampling unit. This is a typical way that you layout data if you are using Excel because that’s how you have to have your data to be able to make charts and carry out most forms of analysis.

In scientific recording format each column is a variable; you have response variables and predictor variables. This recording-layout is a more powerful and ultimately flexible layout because you can add new variables or observations easily. In R this layout is also essential for any kind of complicated analysis, such as regression or analysis of variance.

When you have data in the “wrong” layout you need to be able to rearrange them into a more “sensible” layout so that you can unleash the power of R most effectively. The stack() command is a useful tool that can help you achieve this layout.

The simplest situation is where you have a single response variable and a single predictor, but the values are laid out in sample columns. Here is an example, the data are counts of a freshwater invertebrate at three different sampling locations:

hog3
  Upper Mid Lower
1     3   4    11
2     4   3    12
3     5   7     9
4     9   9    10
5     8  11    11
6    10  NA    NA
7     9  NA    NA

Note that the samples contain different numbers of observations. The “short” columns are padded by NA items when the data is read into R (usually via theread.csv() command).

The stack() command will take the columns and combine them into a single response variable. The names of the columns will be used to form the levels of a predictor variable.

stack(hog3)
   values   ind
1       3 Upper
2       4 Upper
3       5 Upper
4       9 Upper
5       8 Upper
6      10 Upper
7       9 Upper
8       4   Mid
9       3   Mid
10      7   Mid
11      9   Mid
12     11   Mid
13     NA   Mid
14     NA   Mid
15     11 Lower
16     12 Lower
17      9 Lower
18     10 Lower
19     11 Lower
20     NA Lower
21     NA Lower

Note that the columns are labelled, values and ind. You can alter these easily enough using the names() command.

hog = stack(hog3)

names(hog) <- c("count", "site")

head(hog)
  count  site
1     3 Upper
2     4 Upper
3     5 Upper
4     9 Upper
5     8 Upper
6    10 Upper

Now you have a data.frame in recording layout, but the NA items are still in the data.

The na.omit() command will “knock-out” any NA items.

hog
   count  site
1      3 Upper
2      4 Upper
3      5 Upper
4      9 Upper
5      8 Upper
6     10 Upper
7      9 Upper
8      4   Mid
9      3   Mid
10     7   Mid
11     9   Mid
12    11   Mid
13    NA   Mid
14    NA   Mid
15    11 Lower
16    12 Lower
17     9 Lower
18    10 Lower
19    11 Lower
20    NA Lower
21    NA Lower

na.omit(hog)
   count  site
1      3 Upper
2      4 Upper
3      5 Upper
4      9 Upper
5      8 Upper
6     10 Upper
7      9 Upper
8      4   Mid
9      3   Mid
10     7   Mid
11     9   Mid
12    11   Mid
15    11 Lower
16    12 Lower
17     9 Lower
18    10 Lower
19    11 Lower

Note that the row names keep their original values.

If you want to re-number the rows just use the row.names() command.

hog <- na.omit(hog)

row.names(hog) <- 1:length(hog$count)
hog
   count  site
1      3 Upper
2      4 Upper
3      5 Upper
4      9 Upper
5      8 Upper
6     10 Upper
7      9 Upper
8      4   Mid
9      3   Mid
10     7   Mid
11     9   Mid
12    11   Mid
13    11 Lower
14    12 Lower
15     9 Lower
16    10 Lower
17    11 Lower

It’s not really essential but it makes it easier to keep track of items if the numbers are sequential.

You may have data in a more complicated arrangement, perhaps mirroring the on-ground layout. This can happen for example in a two-way ANOVA design. In Excel the data have to be set out in a particular way for the Analysis ToolPak to carry out the anova. However, in R this layout is not helpful.

wp
  Water vulgaris sativa
1    Lo        9      7
2    Lo       11      6
3    Lo        6      5
4   Mid       14     14
5   Mid       17     17
6   Mid       19     15
7    Hi       28     44
8    Hi       31     38
9    Hi       32     37

This time you have a column representing one of the predictor variables and the other columns in sample layout. If you try a stack() command you’ll find that only the numeric columns are stacked.

wps <- stack(wp)
Warning message:
In stack.data.frame(wp) : non-vector columns will be ignored

wps
   values      ind
1       9 vulgaris
2      11 vulgaris
3       6 vulgaris
4      14 vulgaris
5      17 vulgaris
6      19 vulgaris
7      28 vulgaris
8      31 vulgaris
9      32 vulgaris
10      7   sativa
11      6   sativa
12      5   sativa
13     14   sativa
14     17   sativa
15     15   sativa
16     44   sativa
17     38   sativa
18     37   sativa

This is a minor problem because you can re-build the first predictor variable by duplicating the original and adding it to the new data.frame.

What you must do is take the original predictor variable and repeat it a number of times (equal to the number of sample columns).

wps <- cbind(wps, water = rep(wp$Water, 2))
   values      ind water
1       9 vulgaris    Lo
2      11 vulgaris    Lo
3       6 vulgaris    Lo
4      14 vulgaris   Mid
5      17 vulgaris   Mid
6      19 vulgaris   Mid
7      28 vulgaris    Hi
8      31 vulgaris    Hi
9      32 vulgaris    Hi
10      7   sativa    Lo
11      6   sativa    Lo
12      5   sativa    Lo
13     14   sativa   Mid
14     17   sativa   Mid
15     15   sativa   Mid
16     44   sativa    Hi
17     38   sativa    Hi
18     37   sativa    Hi

Note that you gave the new variable an explicit name. The other column names need replacing using the names() command:

names(wps)[1:2] = c("height", "species")

names(wps)
[1] "height"  "species" "water"

head(wps)
  height  species water
1      9 vulgaris    Lo
2     11 vulgaris    Lo
3      6 vulgaris    Lo
4     14 vulgaris   Mid
5     17 vulgaris   Mid
6     19 vulgaris   Mid

Now you have a more sensible layout that can be used for aov() and graphical commands.

This approach will not work on every dataset that you get but it will take you a long way forward.

Exercise 8.3.2.

This exercise is concerned with correlation (Chapter 8) and in particular how you can use Excel to calculate Spearman’s Rank correlation coefficient.

• Introduction
• Pearson correlation
• Spearman’s Rank correlation
  • Rank the data
  • Compute Rs
  • Get a t-value
  • Determine statistical significance

Excel has built-in functions that can calculate correlation, but only when data are normally distributed. The CORREL and PEARSON functions both calculate Pearson’s Product Moment, a correlation coefficient. Once you have the correlation coefficient it is fairly easy to calculate the statistical significance. You compute a t-value then use TINV to compute a critical value or TDIST to get an exact p-value (see Section 8.3.1 in the book).

This exercise shows how you can use the RANK.AVG function to rank the data, then use CORREL on the ranks to obtain a Spearman Rank coefficient.

The data for this exercise are freshwater correlation.xlsx, which look like this:

Example data for practice with correlation. Abundance of invertebrates and water speed.

These data represent the abundance of a freshwater invertebrate and water speed.

To get the regular Pearson correlation you can use the CORREL (or PEARSON) function. In the spreadsheet the data are in columns B and C. To get the correlation coefficient the function required is:

=CORREL(B2:B11, C2:C11)

The result (0.614) is the “regular” parametric correlation coefficient, which is based on the normal distribution. The PEARSON function gives an identical result (Pearson’s Product Moment). Regression is another term used for correlation with parametric (normally distributed or Gaussian) data. The correlation coefficient, r, between two normally distributed variables is calculated like so:

Once you have r you can determine a critical value or exact p-value.

Use Pearson’s correlation when you have normally distributed data and when you expect there to be a linear relationship between the two variables.

Spearman’s Rank correlation is used to determine the strength of the relationship between two numerical variables. The data do not have to be normally distributed and the relationship does not have to be strictly linear either. The relationship should be one where the variables are generally headed in one direction though (so not U-shaped or the inverse).

As the name suggests, the correlation is based on the ranks of the data. The x and y variables are ranked and the ranks of x are compared to the ranks of y like so:

In the formula D is the difference between ranks, and n is the number of pairs of data.

There is no built-in function to work out Spearman Rank correlation but you can work out the ranks using RANK.AVG and calculate the terms in the formula easily enough.

The RANK.AVG function allows you to rank data (note the older RANK function is not the same). In statistical analysis you want the lowest value to have the smallest rank. Tied values should get a tied rank.

To try this out using the example data do the following:

1. Open the freshwater correlation.xlsx The variables are in columns B & C.
2. In cell D1 type a label, RA, for the heading of the Ranks of the Abund variable.
3. In E1 type a label, RS, for the ranks of the Speed variable.
4. Now in D2 type a formula to get the rank of the first datum (in cell B2): =RANK.AVG(B2,B$2:B$11,1). Note the $ used to “fix” the rows references. The final 1 ensures the ranks are ascending order.
5. Copy the formula down the rest of the column to fill cells D2:D11.
6. Copy the cells in D2:D11 across to column E to get ranks in cells E2:E11.

If you wanted to fill out the Spearman Rank formula “the long way” you would now have to subtract each rank in column E from its counterpart in column D. Then square the differences.

However, there is another (easier) way.

Now you have ranks you can use the CORREL (or PEARSON) function on the ranks to get a close approximation of the Spearman Rank correlation coefficient. It is usually the same to at least 2 decimal places.

1. In cell A13 type a label, Correl, for the correlation coefficient(s).
2. In B13 type a formula to work out Pearson’s correlation coefficient for the original data: =CORREL(B2:B11,C2:C11). The result is 0.614 for the example data.
3. In D13 type a formula to work out the correlation between the ranks (i.e. columns D & E): =CORREL(D2:D11,E2:E11). The result is 0.771.

The correlation between the ranks is a close approximation to the Spearman Rank coefficient (0.773) computed the “long way”.

You can compare your calculated Spearman Rank coefficient to a table of critical values (e.g. Table 8.5 in the book) or compute a t-value (another approximation).

To get a value for t you need the correlation coefficient and the number of pairs of values. These then go into the following formula:

The df in the formula is degrees of freedom and is the number of pairs of data minus 2. In the example datafile:

1. In cell A14 type a label, n, for the number of pairs of data.
2. In B14 type a formula to work out the number of items you have: =COUNT(B2:B11). You get 10.
3. In A15 type a label, df, for the degrees of freedom.
4. In B15 type a formula to work out degrees of freedom: =B14-2. You get 8.
5. In A16 type a label, t, for the t-value.
6. In B16 type a formula to work out a t-value: =SQRT(D13^2*B15/(1-D13^2)). The result is 3.420.

Once you have a value for t you are on your way to determining the statistical significance.

Now you have a value for t you can:

• Get a critical value using the TINV function.
• Get an exact probability (a p-value) using the TDIST function.

The TINV function requires the significance level (usually 0.05) and the degrees of freedom.

The TDIST function requires a t-value, the degrees of freedom and a 2 (for a two-tailed test, which is most often what you need).

1. In cell A17 type a label, t-crit, for the critical value.
2. In B17 type a formula to calculate the critical t value: =TINV(0.05,B15). The result is 2.306.
3. In A18 type a label, p-value, for the exact probability.
4. In B18 type a formula to calculate the exact p-value: =TDIST(B16,B15,2). The result is 0.009.

You can compare your calculated value of t to the critical value and simply say that your correlation is significant at p < 0.05 or you can use the exact p-value (of 0.009).

This method of calculating Spearman Rank gives a good approximation of the correlation coefficient but the p-values are a little conservative. This is the method that the base distribution of R uses in its cor.test() command when method=”spearman”.

Exercise 7.3.3.

This exercise is concerned with matched pairs tests (Section 7.3) and in particular how to carry out the non-parametric Wilcoxon Matched Pairs test using Excel (Section 7.3.3).

• Introduction
  • The example data
• Show the direction of the difference
• Calculate the differences
• Calculate ranks of differences
• Ranks of + and of – differences
• Rank sums
• Final result

There is no in-built function that will carry out the Wilcoxon Matched Pairs test in Excel. However, you can rank the data and compute the rank sums you require using the RANK.AVG function.

You do need to omit zero differences from the calculations and also to separate ranks of positive differences from ranks of negative differences. In this exercise you can see how to use the IF function to help you do this separation.

You can get the sample data here: Wilcoxon.xlsx. There are two worksheets, one has the data only and the other has a completed set of calculations so you can check your progress.

Here’s what the data look like:

Matched pairs data for use in Wilcoxon matched pairs analysis

You can see that there are 8 pairs of data.

Start by making a column to show the direction of the difference. Make a heading in cell D1, Dir will do as a heading name.

Now in cell D2 type a formula to show if the difference between B2 and C2 is positive or negative. You’re going to subtract the second column from the first. You also want to take into account possible zero differences:

=IF(B2<C2, "-", IF(B2=C2, "", "+"))

So if cell C2 is larger than cell B2 you’ll get a minus symbol. If the cells are equal, then you get a blank. If B2 is larger than C2 you’ll get a + symbol. You will use these symbols to separate the ranks later.

Copy the formula down the rest of the column to show the direction of each of the differences.

The final column shows the direction of differences between A and B (i.e. A-B).

Note that observation 5 (row 6 of the spreadsheet) shows a blank because the items are the same (i.e. a zero difference).

Make a column for the difference between samples, make the heading in cell E1, D(A-B) or something similar.

You want to subtract the values in the second column of data from the first column of data (i.e. Col B – Col C). So in cell E2 type a formula to do that. You’ll need to omit any zero difference, so use an IF function to place a blank “” if the difference is zero:

=IF(B2-C2=0, "", ABS(B2-C2))

You are not interested in the sign of any difference, just the magnitude, so the ABS function is needed.

Copy the result down the rest of the column.

The final column shows the absolute magnitude of differences between A and B.

You can see that you have 7 values, with one blank (a zero difference, observation 5).

Now you want to work out the ranks of the differences. That is the ranks of the absolute value of the differences that you just worked out (column E) and called D(A-B).

Make a new column label in cell F1, call it Rd or something similar.

Now in cell F2 type a formula to work out the ranks of the items from column E:

=IF(E2="", "", RANK.AVG(E2,$E$2:$E$9,1))

Note that you need to take care of any possible blank cells (corresponding to zero differences). You also need to “fix” the cell range E2:E9 using $ since you will be copying the cell down the rest of the column. The final 1 makes sure that your ranks are sorted ascending order, with the smallest difference getting the smallest rank.

The final column shows the rank of the (absolute) differences between A and B.

You can see that there are some tied ranks (each given a value of 2.5).

Now you need to separate the ranks due to the positive differences and the ranks due to negative differences. Mae two more column headings in cells G1 and H1, R+ and R- will do fine.

In cell G2 type a formula that shows the rank if it is due to a positive difference but leaves the cell blank is not:

=IF(D2="+", F2,"")

Copy the cell down the rest of the column G.

In cell H2 type a formula that shows the rank if it is due to a negative difference but leaves the cell blank is not:

=IF(D2="-", F2,"")

Copy the cell down the rest of the column H.

The last two columns show the ranks due to positive differences and negative differences.

You should now see the ranks split according to the sign of the difference between the samples.

Now you need to add up the ranks for the positive and negative differences (columns G & H). You can use the SUM function for this.

In cells A11 and A12 type labels for the counts and sums, n and Sum, will do nicely.

In cell B11 type a formula to work out the number of observations:

=COUNT(B2:B9)

Copy this into cells C11 and F11. The latter being the number of non-zero differences.

In cell B12 type a formula to sum the data in column 12:

=SUM(B2:B9)

Copy the cell into cells C12, F12, G12 and H12. Cells F12:H12 contain the ranks sums; overall, +ve and -ve. Note that the overall sum of ranks should equal the two other rank sums combined.

The final two rows show some summary (count and sum). The test statistic is the smaller of the two rank sums (12 & 16).

The two rank sums are 12 and 16 so the 12 is the test statistic, W. You’ll need the number of non-zero differences to look up the appropriate critical value (see Table 7.13 in the book).

For Nd = 7 the critical value is 2. The calculated value of W is 12, which is larger than the critical value so the result is not significant.

Exercise 7.1.2.

This exercise is concerned with how to carry out the t-test (Chapter 7) using R (Section 7.1.2).

• Introduction
• Separate data objects
• Data in sample layout
  • Use $ syntax
  • Use attach()
  • Use with()
• Data in recording layout

The t-test is used to compare the means of two samples that have a normal (parametric or Gaussian) distribution. The t.test() command carries out the t-test in R. The default is to compute the Welch two-sample test (unequal variances).

You can have your data in several forms:

• Two separate samples as two data vectors.
• Two separate samples but in a single data.frame object (i.e. sample format).
• A response variable and a predictor variable (i.e. scientific recording format)

In any event you can use the t.test() command to carry out the t-test. The example data for this exercise are the same as in the book and you can get the data in the three forms as an RData file: ridge furrow.RData.

Once you have the data you can type the commands shown here for yourself and so follow along.

When you have two separate samples (probably as vector objects), you can just name them in the t.test() command.

ridge ; furrow
[1] 4 3 5 6 8 6 5 7
[1]  9  8 10  6  7

t.test(ridge, furrow)

Welch Two Sample t-test

data:  ridge and furrow
t = -2.7584, df = 8.733, p-value = 0.02279
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 -4.5598309 -0.4401691
sample estimates:
mean of x mean of y
      5.5       8.0

The default carries out the Welch two-sample test (with modified degrees of freedom).

To carry out a t-test with the assumption that the variances are equal, you need to add var.equal = TRUE like so:

t.test(ridge, furrow, var.equal = TRUE)

The result gives a slightly different value for t, df and p-value.

If your data are separate samples but contained within a data.frame, you’ll need to alter your approach very slightly so that you can “get at” the variables in the data.frame.

There are three main ways:

• Use $ syntax to specify the frame and sample name explicitly.
• Use attach() to place the variables in the search path.
• Use with() to open the frame temporarily.

Here are the example data:

rf2
  Ridge Furrow
1     4      9
2     3      8
3     5     10
4     6      6
5     8      7
6     6     NA
7     5     NA
8     7     NA

Note that the shorter sample is padded with NA items.

You can specify a variable by using the name of the enclosing object, a $ and the variable name:

t.test(rf2$Ridge, rf2$Furrow)

Welch Two Sample t-test

data: rf2$Ridge and rf2$Furrow
t = -2.7584, df = 8.733, p-value = 0.02279

Note that R presents the name exactly as you typed it in the command.

If you try to use a variable that is “inside” a data.frame you get an error:

Ridge
Error: object ‘Ridge’ not found

One way around this is to use attach() to “open” the data.frame and allow the separate variables to be found in the search path. Once you have attached an object its contents appear when you use the search() command and can be used without needing the $ syntax.

Type search() to see the current search path (this is an example):

search()
[1] ".GlobalEnv"        "tools:RGUI"        "package:stats"
[4] "package:graphics"  "package:grDevices" "package:utils"
[7] "package:datasets"  "package:methods"   "Autoloads"
[10] "package:base"

Use attach() to open the data object you want:

attach(rf2)

The rf2 object now appears in the search path:

search()
[1] ".GlobalEnv"        "rf2"               "tools:RGUI"
[4] "package:stats"     "package:graphics"  "package:grDevices"
[7] "package:utils"     "package:datasets"  "package:methods"
[10] "Autoloads"        "package:base"

Now you can use the variables within rf2 in your t-test:

t.test(Ridge, Furrow)

Welch Two Sample t-test

data: Ridge and Furrow
t = -2.7584, df = 8.733, p-value = 0.02279

Note that R presents the names exactly as you typed them.

You should use detach() after you are done. This removes the item from the search() path. You can get confusion if you have data objects with the same name as those contained within data.frames.

detach(rf2)

The attach() command will not overwrite any data objects, but if you open a data.frame and it contains items with the same names as existing objects, the attach()ed ones mask the others until you use detach().

The attach() command is useful but you do need to be careful to use detach() after you are done. An alternative approach is to use the with() command, which acts like attach() but only for the duration of one command line.

with(data.name, ...)

So, you give the command the name of the data object you want to “open”, followed by the command you want to execute. In that command you can give the variable names as they are and don’t need the $ syntax.

with(rf2, t.test(Ridge, Furrow, var.equal = TRUE)

In the example the variance is considered equal.

So, you don’t have to use detach() afterwards.

If your data are in scientific recording format, then you’ll have the data in a different form from that shown previously (sample format). You will have response variables and predictor variables. For a t-test you will have one response and one predictor e.g.

rf1
   count   area
1      4  Ridge
2      3  Ridge
3      5  Ridge
4      6  Ridge
5      8  Ridge
6      6  Ridge
7      5  Ridge
8      7  Ridge
9      9 Furrow
10     8 Furrow
11    10 Furrow
12     6 Furrow
13     7 Furrow

This layout is more flexible than sample format but you need a slightly different way to specify the variables in your t-test. Essentially you give a formula of form: y ~ x where y is the response (count) and x is the predictor (area). You can also give the name of the enclosing data object.

t.test(count ~ area, data = rf1)

Welch Two Sample t-test

data:  count by area
t = 2.7584, df = 8.733, p-value = 0.02279

Note that R presents the names of the variables as they appear in the enclosing data object.

The formula syntax is very powerful and is used in many statistical and graphical commands. You can extend the formula for more complicated scenarios, such as analysis of variance and multiple regression.

Exercise 7.1.1

This exercise is concerned with using Excel for the t-test in Chapter 7 (Section 7.1). In particular you’ll see how to modify the degrees of freedom for cases when the variance of two samples is not equal (which is often).

• Introduction
• Calculation
• Carry out basic t-test
• Compute modified degrees of freedom
• Use the Analysis ToolPak

The t-test is used to compare the means of two samples that have a normal (parametric or Gaussian) distribution. The “classic” t-test has two major variants:

• Assumption of equal variance for the two samples.
• Adjustment of degrees of freedom (Satterthwaite modification).

In the first case the common variance is calculated and used in place of the variance in the regular formula. The calculation for this is relatively simple but it is also pointless, since you still have to determine the variance of the two samples.

The most commonly used modification is to adjust the degrees of freedom to make the result of the t-test a little more conservative. The degrees of freedom are reduced slightly using the Satterthwaite modification. This version of the t-test is generally called the Welch 2-sample t-test.

The calculations are relatively easy. You can then use the modified df for looking up critical values or for computing the exact p-value. The Welch 2-sample t-test is carried out by default in R via the t.test() command. In Excel the TTEST function will give you the exact p-value but it will not provide the modified degrees of freedom.

The Excel functions TDIST and TINV will give incorrect results as they assume equal variance and use un-modified degrees of freedom. This exercise works through the t-test and shows how to use the Satterthwaite modification to alter degrees of freedom. This allows you to get the “proper” result in Excel. The calculation matches that used in the Analysis ToolPak, which is available in Windows versions of Excel and later Mac versions.

The exercise uses the data that you can see in the following table:

Abundance of R. repens in ridges and furrows of a mediaeval meadow

The data show the abundance of a plant species in two different habitats. The two samples are small but are normally distributed. You can get a copy of the data in Excel .xlsx format here: ridge furrow.xlsx.

The calculation of the modified degrees of freedom are in two parts. To start with you determine a statistic called u, which you can think of as a kind of proportion (it varies from 0–1).

Once you have a value for u, you can determine f, the modified degrees of freedom.

The formula gives the same value whichever sample you choose to be #1 and which #2. The df are reduced slightly from the original: original df = (n1 – 1 + n2 – 1).

Once you have a modified df you can use it to look up the critical value, either in a table or using the TINV function in Excel. The equivalent in R would be the qt() function.

Start by opening the ridge furrow.xlsx data file. Go to the Data worksheet (the t-test completed worksheet is provided for you to check your results). The two samples are in columns B and C.

1. In cell A10 type a label for the number of observations in each sample, “n” will do nicely.
2. In cell B10 type a formula to determine the number of observations =COUNT(B2:B9)
3. Copy cell B10 into C10 so that you have a result for each sample
4. In cell A11 type a label for the mean values, “mean” will do fine.
5. In cell B11 type a formula to work out the mean = AVERAGE(B2:B9).
6. Copy cell B11 into C11 so that you have a result for each sample.
7. In cell A12 type a label for the variance, “variance” seems logical.
8. In cell B12 type a formula to calculate the variance =VAR(B2:B9)
9. Copy the variance formula from B12 to C12.
10. In A13 type a label for the t-value, “t” or “t-value” will do.
11. In cell B13 type a formula to work out the value of t: =ABS(B11-C11)/SQRT(B12/B10+C12/C10)
12. In A15 (yes, leave a blank row) type a label “p-value”.
13. In B15 type a formula to work out the p-value based on the t you calculated: =TDIST(B13,B10+C10-2,2)
14. The p-value from step 13 (p = 0.019) is based on equal variance (and un-modified degrees of freedom) and so is not really correct. Carry on and calculate a critical value.
15. In A17 type a label “t-crit”.
16. In B17 type a formula to work out a critical value for t: =TINV(0.05,B10+C10-2)

The critical value from step 15 (t = 2.201) is also based on equal variance and un-modified degrees of freedom.

1. In A18 type another label “p-value”, for the exact p-value computed by Excel’s TTEST function.
2. In B18 type a formula to compute the exact p-value for a t-test with un-equal variance: =TTEST(B2:B9,C2:C6,2,3)

Note that the TTEST function gives a more conservative p-value (of 0.023) because it has calculated the modified degrees of freedom. However, you cannot extract the df directly and will have to compute it if you want to report a more appropriate critical value you’ll need to compute it longhand.

The calculation of the modified degrees of freedom (Satterthwaite modification) will require some simple maths using the equations from earlier:

1. In Cell A20 type a label “u” for the result of the first calculation.
2. In B20 type a formula to calculate u: =(B12/B10)/((B12/B10)+(C12/C10)).
3. In A21 type a label “f” for the result of the second calculation.
4. In B21 type a formula to calculate f: =1/(((B20^2)/(B10-1))+(((1-B20)^2)/(C10-1)))
5. Your result from step 21 should be 8.733. Excel cannot deal with degrees of freedom that are not integer values so you need to round up (Excel always rounds the df value upwards):
6. In A22 type a label “df” for the integer df result.
7. In B22 type a formula to round up the value from step 21: =CEILING(B21,1)
8. Now you have a modified df (df = 9) you can use it to calculate a critical value. You can also see how the modified df can be used in TINV and TDIST functions.
9. In A24 type a label “t-crit” for the new critical value.
10. In B24 type a function to compute the critical value based on the new df: =TINV(0.05,B22)
11. Note that the critical value from step 25 (t = 2.262) is higher than the value from step 15, and so a bit more conservative.
12. In A25 type a label “p-value” for the exact p-value based on the modified df.
13. In B25 type a formula to work out the exact p-value: =TDIST(B13,B22,2)

Note that the exact p-value from step 27 (p = 0.022) is very similar to that you obtained from the TTEST function in step 17. If you have a p-value you can get the value of t but only if you have the modified degrees of freedom.

11. In A26 type a label “t-value” for the t calculation.
12. In B26 type a formula that calculates the value of t based on the p-value and modified df: =TINV(B25,B22)

The final t-value you calculated in step 29 (t = 2.758) is the same as the one from step 11. Unfortunately, because of slight rounding errors you usually get a slightly different p-value using the TTEST function compared to the “long” method. The upshot is that it is always better to calculate the value of t using the means and variance, rather than indirectly via TTEST and TINV.

You can use Analysis ToolPak add-in to carry out a t-test. This allows you to get the value of t, the critical value and the exact p-value all at once. The ToolPak is not always “activated” so you need to go to the options and find the Add-Ins (the exact method will depend on your version of Excel).

1. Open the ridge furrow.xlsx data file.
2. Click the Data > Data Analysis button to open the Data Analysis dialogue window.
3. Choose the t-test: Two-Sample Assuming Unequal Variances
4. Now select the appropriate data and fill in the boxes. Make sure you type a zero in the box labelled Hypothesized Mean Difference

   

    

5. Choose the location to place the results. This can be a new workbook or worksheet. In the example above the results are placed in cell E2 of the existing sheet.

Now you just have to interpret the results!:

You can ignore the sign for the t Stat result, if the samples were in different column order the result would be the same value but with opposite sign.

Note that the df are shown as an integer and the exact value is always rounded upwards.

You want the two-tail results in most cases, which are in the last two rows.