### Tags: analyse-it, analysis, anova, built-in, clear, excel, function, inerst13, instructions, microsoft, msdn, post-hoc, software, tukey, web, xls

# anova post-hoc analysis: tukey test

On Microsoft » Microsoft Excel

8,119 words with 5 Comments; publish: Wed, 04 Jun 2008 18:48:00 GMT; (30693.75, « »)

excel does not have such a built-in function so i tried analyse-it and

INERST13.XLS from web. both don't have any clear instructions how the

dataset should be input. is anybody knowing how to best perform such a test

in Excel? thx in advance!!

*http://excel.todaysummary.com/q_microsoft-excel_20149.html*

All Comments

Leave a comment...

- 5 Comments
- Tukey did a lot of things. In the context of ANOVA, I will assume that you
mean his single degree of freedom to test for non-additivity.

You are correct that Excel has no native function to do this. Moreover, the

calculations do not lend themselves easily to an array formula. However it

is not difficult to write a UDF to do the calculations. The following UDF

assumes that you have complete data arranged in rows and columns (for

example, treatments and blocks). You pass that rectangular array of data to

the function, as in

=Tukey1df(A1:C5)

to get the p-value for non-additivity. Other components of the ANOVA are

identified by comments in the code.

If you are using an early version of Excel that does not support the

WorksheetFunction keyword, then replace WorksheetFunction with Application

and the code should work.

Jerry

Function Tukey1df(y)

Dim rAveD(), cAveD() ' arrays of deviations of row/column averages

from grand mean

Dim r As Long, c As Long, dfe As Long, i As Long, j As Long, _

Ave As Double, denom1 As Double, denom2 As Double, _

SSr As Double, SSc As Double, SSTukey As Double, SStot As Double,

MSe As Double, FTukey As Double

r = y.Rows.Count

c = y.Columns.Count

If Application.Count(y) <> r * c Then Tukey1df = [#VALUE!]: Exit Function

ReDim rAveD(1 To r), cAveD(1 To c)

Ave = WorksheetFunction.Average(y)

denom2 = 0

For j = 1 To c

cAveD(j) = WorksheetFunction.Average(y.Columns(j)) - Ave

denom2 = denom2 + cAveD(j) ^ 2

Next j

denom1 = 0

SSTukey = 0

For i = 1 To r

rAveD(i) = WorksheetFunction.Average(y.Rows(i)) - Ave

denom1 = denom1 + rAveD(i) ^ 2

For j = 1 To c

SSTukey = SSTukey + y(i, j) * rAveD(i) * cAveD(j)

Next j

Next i

SSTukey = SSTukey ^ 2 / denom1 / denom2 ' SS for non-additivity

SSr = WorksheetFunction.DevSq(rAveD) * c ' SS for rows

SSc = WorksheetFunction.DevSq(cAveD) * r ' SS for columns

SStot = WorksheetFunction.DevSq(y) ' SS for (corrected) total

dfe = (r - 1) * (c - 1) - 1

MSe = (SStot - SSr - SSc - SSTukey) / dfe ' MS for error (residual)

FTukey = SSTukey / MSe ' F for non-additivity

Tukey1df = WorksheetFunction.FDist(FTukey, 1, dfe) ' p-value for

non-additivity

End Function

"Ross" wrote:

> excel does not have such a built-in function so i tried analyse-it and

> INERST13.XLS from web. both don't have any clear instructions how the

> dataset should be input. is anybody knowing how to best perform such a test

> in Excel? thx in advance!!

#1; Wed, 04 Jun 2008 18:49:00 GMT

- Tukey did a lot of things. In the context of ANOVA, I will assume that you
- Sorry, a Google search shows that your description is apparently specific in
the psychology literature to what the statistics literature would call

Tukey's HSD multiple comparison procedure.

The calculations are straightforward

http://davidmlane.com/hyperstat/B95118.html

http://web.umr.edu/~psyworld/tukeyssteps.htm

except for determining p-values or critical values. For that, you could

translate

http://lib.stat.cmu.edu/apstat/190

from Fortran into VBA if you do not care to use a table.

Jerry

"Ross" wrote:

> excel does not have such a built-in function so i tried analyse-it and

> INERST13.XLS from web. both don't have any clear instructions how the

> dataset should be input. is anybody knowing how to best perform such a test

> in Excel? thx in advance!!

#2; Wed, 04 Jun 2008 18:50:00 GMT

- Sorry, a Google search shows that your description is apparently specific in
- thanks, jerry. there're typos and corrected for those who may also need
this. on the other hand, i don't know what "single degree of freedom to test

for non-additivity" is and i wonder if a ranking saying which pairs are

significantly different, thanks again!

> Dim rAveD(), cAveD() ' arrays of deviations of row/column averages

> from grand mean

Dim rAveD(), cAveD() ' arrays of deviations of row/column averages from

grand mean

> SSr As Double, SSc As Double, SSTukey As Double, SStot As Double,

> Tukey1df = WorksheetFunction.FDist(FTukey, 1, dfe) ' p-value for

> non-additivity

Tukey1df = WorksheetFunction.FDist(FTukey, 1, dfe) ' p-value for

non-additivity

#3; Wed, 04 Jun 2008 18:51:00 GMT

- thanks, jerry. there're typos and corrected for those who may also need
- You're welcome, glad it helped.
I'm not sure what you are calling a typo. Both your quoted and retyped

lines have identical content, so I assume that that the issue was

wrapping of long lines in my post.

Jerry

Ross wrote:

> thanks, jerry. there're typos and corrected for those who may also need

> this. on the other hand, i don't know what "single degree of freedom to test

> for non-additivity" is and i wonder if a ranking saying which pairs are

> significantly different, thanks again!

>

>> Dim rAveD(), cAveD() ' arrays of deviations of row/column averages

>> from grand mean

> Dim rAveD(), cAveD() ' arrays of deviations of row/column averages from

> grand mean

>

>> SSr As Double, SSc As Double, SSTukey As Double, SStot As Double,

>> Tukey1df = WorksheetFunction.FDist(FTukey, 1, dfe) ' p-value for

>>non-additivity

> Tukey1df = WorksheetFunction.FDist(FTukey, 1, dfe) ' p-value for

> non-additivity

#4; Wed, 04 Jun 2008 18:52:00 GMT

- You're welcome, glad it helped.
- Not sure why you had trouble with InerSTAT-a. The inputs are clearly
labeled. You do not input the dataset, you input summary statistics

(treatment means, standard deviations, and sample sizes).

However, InerSTAT-a v1.3 calculates critical values for Tukey's HSD from

only 3 terms of an asymptotic expansion. Consequently they are

inaccurate for small degrees of freedom. InerSTAT-a v1.3 results should

be reliable for df>=10.

The table at

http://web.umr.edu/~psyworld/virtualstat/tukeys/criticaltable.html

should be accurate to all figures given, since it is an accurate subset

of Table 29 from the 3rd edition of "Biometrika Tables for

Statisticians" (BTKS3).

I do not know how accurate the p-values calculated by prtrng from

http://lib.stat.cmu.edu/apstat/190

are, but they seem consistent with BTKS3. Critical values calculated by

qtrng are less accurate than numerically inverting prtrng p-values.

Jerry

Jerry W. Lewis wrote:

> Sorry, a Google search shows that your description is apparently specific in

> the psychology literature to what the statistics literature would call

> Tukey's HSD multiple comparison procedure.

> The calculations are straightforward

> http://davidmlane.com/hyperstat/B95118.html

> http://web.umr.edu/~psyworld/tukeyssteps.htm

> except for determining p-values or critical values. For that, you could

> translate

> http://lib.stat.cmu.edu/apstat/190

> from Fortran into VBA if you do not care to use a table.

> Jerry

> "Ross" wrote:

>

>>excel does not have such a built-in function so i tried analyse-it and

>>INERST13.XLS from web. both don't have any clear instructions how the

>>dataset should be input. is anybody knowing how to best perform such a test

>>in Excel? thx in advance!!

#5; Wed, 04 Jun 2008 18:53:00 GMT

- Not sure why you had trouble with InerSTAT-a. The inputs are clearly