PC Review


Reply
Thread Tools Rate Thread

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

 
 
Per Madsen
Guest
Posts: n/a
 
      29th Oct 2003
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

 
Reply With Quote
 
 
 
 
paul falla
Guest
Posts: n/a
 
      30th Oct 2003

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

 
Reply With Quote
 
 
 
 
Per Madsen
Guest
Posts: n/a
 
      30th Oct 2003
paul falla wrote:
>>-try this site.

>
> http://members.aol.com/johnp71/javasta2.html#Excel
> It has many statistical programmes and add-ins which are
> free to download.


Thank you! I'll check it out...
Kindly

Per

 
Reply With Quote
 
Jerry W. Lewis
Guest
Posts: n/a
 
      3rd Nov 2005
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/virtual...icaltable.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
>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't get rid of a specific Excel addin in MSIE - I managed to get rid of it in Excel but not in the excel addin for ie WMB Windows XP Internet Explorer 0 20th Apr 2005 06:43 PM
Excel VBA from Excel 97 to Excel 2000 errors Ben Harshbarger Microsoft Excel Programming 3 15th Jul 2004 09:44 AM
Shared workbook opened in Excel 2002 on a Wk2 server with terminal services & opened Excel 97 on a NT Terminal server - Excel 2002 data being lost (sometimes) =?Utf-8?B?aG5j?= Microsoft Excel Programming 0 13th Mar 2004 12:56 PM
Word2002 and excel... excel ...excel.... Wolvie145 Microsoft Word Document Management 1 8th Mar 2004 04:50 PM
Excel VBA Code Execution Excel XP Vs Excel Office 2000 John Flynn Microsoft Excel Programming 6 24th Aug 2003 04:58 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:27 AM.