anova post-hoc analysis: tukey test

[Ross]

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!!

[Jerry W. Lewis]

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!!

[Jerry W. Lewis]

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!!

[Ross]

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

[Jerry W. Lewis]

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

[Ross]

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

[Jerry W. Lewis]

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

[Jerry W. Lewis]

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/virtual...icaltable.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!!