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

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

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

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

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

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

Hi there! I can definitely help you with performing a Tukey test for ANOVA post-hoc analysis in Excel.

Organizing the Data
Your data should be in columns, with each column representing a different group or treatment. The rows should contain the individual data points for each group. Make sure that your data is properly labeled and formatted.

Calculating the ANOVA
To calculate the ANOVA for your data, select the range of data that you want to analyze, go to the "Data" tab, and click on "Data Analysis" in the "Analysis" group. Select "ANOVA: Single Factor" from the list of analysis tools and click "OK". Follow the prompts to input your data and run the analysis.

Performing the Tukey Test
To perform the Tukey test for post-hoc analysis, you can use a free add-in called "Real Statistics Resource Pack".

To use the Real Statistics Resource Pack, you will need to download and install it. Once installed, you can access the Tukey test function by going to the "Real Statistics" tab in Excel and selecting "ANOVA" from the list of functions. From there, select "Post-hoc Tests" and then "Tukey's HSD". Follow the prompts to input your data and run the analysis.

Let me know if you have any questions or need further assistance.

1. Organize the data in columns with each column representing a different group or treatment.
2. Calculate the ANOVA using the built-in function in Excel.
3. Download and install the free add-in called "Real Statistics Resource Pack".
4. Access the Tukey test function by going to the "Real Statistics" tab in Excel and selecting "ANOVA" from the list of functions.
5. Select "Post-hoc Tests" and then "Tukey's HSD" and follow the prompts to input your data and run the analysis.