Bonferroni/Tukey's-How to do it in Excel

Discussion in 'Microsoft Excel Misc' started by Per Madsen, Oct 29, 2003.

  1. Per Madsen

    Per Madsen Guest

    I originally posted this in microsoft.public.mac.office.excel but was
    advised to post it here aswell:

    I'm afraid I need a little help on this!

    I've done a series of measurements on tissues from the Green Shorecrab.

    The test specimens were divided into four groups (A->D), with 12
    individuals in each. The crabs in each group were destroyed and 8
    different tissue sample's were taken from each individual. An average
    were calculated for each group. I then performed an ANOVA test to see if
    there were differences between the groups in regards to metal content in
    the tissues. The test showed that there indeed were differences between
    the groups (p < 0.05). The task now, is to determind which groups shows
    a significant difference from one another (is it A and B, A and C,
    or...ect), for every type of tissue. This can be done with a Bonferroni
    test or a Tukey's test. Unfortunately these tools are not included in
    the Data Analysis Toolpack. So, my question is: how can I perform a
    Bonferroni or Tukey's test in Excel?

    E.g.

    Tissue: Gills

    A B C D


    12 13 12 12
    .. . . .
    .. . . .
    ..
    ..
    ..
    ..
    ..
    ..
    ..
    ..
    ..
    13 12 14 15
    --------------------------------------------------

    Average:
    12 13 14 15

    P.S. Sorry 'bout my broken english

    Thx in advance!!!

    Per Madsen, Denmark

    www.madsen.blogdrive.com
     
    Per Madsen, Oct 29, 2003
    #1
    1. Advertisements

  2. Per Madsen

    paul falla Guest


    >-try this site.

    http://members.aol.com/johnp71/javasta2.html#Excel
    It has many statistical programmes and add-ins which are
    free to download.
    ---Original Message-----
    >I originally posted this in

    microsoft.public.mac.office.excel but was
    >advised to post it here aswell:
    >
    >I'm afraid I need a little help on this!
    >
    >I've done a series of measurements on tissues from the

    Green Shorecrab.
    >
    >The test specimens were divided into four groups (A->D),

    with 12
    >individuals in each. The crabs in each group were

    destroyed and 8
    >different tissue sample's were taken from each

    individual. An average
    >were calculated for each group. I then performed an ANOVA

    test to see if
    >there were differences between the groups in regards to

    metal content in
    >the tissues. The test showed that there indeed were

    differences between
    >the groups (p < 0.05). The task now, is to determind

    which groups shows
    >a significant difference from one another (is it A and B,

    A and C,
    >or...ect), for every type of tissue. This can be done

    with a Bonferroni
    >test or a Tukey's test. Unfortunately these tools are not

    included in
    >the Data Analysis Toolpack. So, my question is: how can I

    perform a
    >Bonferroni or Tukey's test in Excel?
    >
    >E.g.
    >
    >Tissue: Gills
    >
    >A B C D
    >
    >
    >12 13 12 12
    >.. . . .
    >.. . . .
    >..
    >..
    >..
    >..
    >..
    >..
    >..
    >..
    >..
    >13 12 14 15
    >--------------------------------------------------
    >
    >Average:
    >12 13 14 15
    >
    >P.S. Sorry 'bout my broken english
    >
    >Thx in advance!!!
    >
    >Per Madsen, Denmark
    >
    >www.madsen.blogdrive.com
    >
    >.
    >
     
    paul falla, Oct 30, 2003
    #2
    1. Advertisements

  3. Per Madsen

    Per Madsen Guest

    Per Madsen, Oct 30, 2003
    #3
  4. I just stumbled across this thread, and am answering for the benefit of
    those searching the archives (hopefully the OP has long since finished
    this analysis).

    Within a tissue type, there are k=4 groups with n=12 observations per
    group.

    To test the difference between a PRE-SPECIFIED pair of groups, you would
    use the t statistic
    t = (ave1-ave2)/(S*SQRT(2/12))
    where S is the pooled estimate of standard deviation (since a basic
    assumption for ANOVA is that the variance is the same within each group)
    S = SQRT(MSE) = SQRT((devsq1+devsq2+devsq3+devsq4)/44)
    based on 44=k*(n-1) degrees of freedom. The critical value for this
    test would be 2.02=TINV(0.05,44).

    The shortcoming of the preceding discussion is that the type I error
    rate is 5% for each test, so with multiple comparisons, the probability
    of an error in at least one comparison is much larger than 5%. In
    particular, to identify unspecified significant differences, you are
    essentially evaluating all pairwise comparisons, which in this case is
    6=COMBIN(4,2) comparisons.

    The Bonferroni approach approximates the overall error rate by assuming
    that each comparison is independent, so that the null hypothesis
    probabilities of non-significance multiply. Hence you would use the
    previously discussed t statistics with a critical value of
    2.75=TINV(1-(1-0.05)^(1/6),44).

    The shortcoming of the Bonferroni approach is that you cannot get six
    independent mean differences among only four independent means. Hence
    the Bonferroni approach is conservative (the true overall error rate
    will be less than 0.05).

    Tukey showed that the actual critical value should be 2.67=3.78/sqrt(2)
    where 3.78 is interpolated from a table of percentage points for the
    studentized range
    http://web.umr.edu/~psyworld/virtualstat/tukeys/criticaltable.html
    (k=4, df=44).

    If you want to avoid using a table,
    http://lib.stat.cmu.edu/apstat/190
    gives Fortran code for calculating the p-value
    (1-prtrng(3.78,44,4,ifault)) or the critical value
    (qtrng(1-0.05,44,4,fault)) for the studentized range.

    Since Tukey's HSD multiple comparison procedure uses studentized range
    tables, it is more common to work with t*SQRT(2) instead of the usual t
    statistic (so you can use the tabled values directly).
    http://web.umr.edu/~psyworld/tukeyssteps.htm

    If the groups do not all have the same number of observations, then it
    is often recommended to use the harmonic mean of the two group sizes
    http://davidmlane.com/hyperstat/B95118.html
    The greater the differences in sample sizes, the more that this is only
    an approximate solution.

    Jerry

    Per Madsen wrote:

    > I originally posted this in microsoft.public.mac.office.excel but was
    > advised to post it here aswell:
    >
    > I'm afraid I need a little help on this!
    >
    > I've done a series of measurements on tissues from the Green Shorecrab.
    >
    > The test specimens were divided into four groups (A->D), with 12
    > individuals in each. The crabs in each group were destroyed and 8
    > different tissue sample's were taken from each individual. An average
    > were calculated for each group. I then performed an ANOVA test to see if
    > there were differences between the groups in regards to metal content in
    > the tissues. The test showed that there indeed were differences between
    > the groups (p < 0.05). The task now, is to determind which groups shows
    > a significant difference from one another (is it A and B, A and C,
    > or...ect), for every type of tissue. This can be done with a Bonferroni
    > test or a Tukey's test. Unfortunately these tools are not included in
    > the Data Analysis Toolpack. So, my question is: how can I perform a
    > Bonferroni or Tukey's test in Excel?
    >
    > E.g.
    >
    > Tissue: Gills
    >
    > A B C D
    >
    >
    > 12 13 12 12
    > . . . .
    > . . . .
    > .
    > .
    > .
    > .
    > .
    > .
    > .
    > .
    > .
    > 13 12 14 15
    > --------------------------------------------------
    >
    > Average:
    > 12 13 14 15
    >
    > P.S. Sorry 'bout my broken english
    >
    > Thx in advance!!!
    >
    > Per Madsen, Denmark
    >
    > www.madsen.blogdrive.com
    >
     
    Jerry W. Lewis, Nov 3, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keeno

    Excel Range Value issue (Excel 97 Vs Excel 2003)

    Keeno, Jun 13, 2005, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    283
    Dave Peterson
    Jun 13, 2005
  2. Guest
    Replies:
    0
    Views:
    481
    Guest
    Jun 27, 2005
  3. Guest
    Replies:
    3
    Views:
    223
    Guest
    Aug 24, 2005
  4. Replies:
    1
    Views:
    169
    Jon Peltier
    Jan 5, 2007
  5. Guest
    Replies:
    0
    Views:
    285
    =?Utf-8?B?TWVsc2g=?=
    Aug 1, 2007
Loading...

Share This Page