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; (30646.88, « »)

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

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