PC Review


Reply
Thread Tools Rate Thread

Formula for Cochran's Critical Values

 
 
Dave Curtis
Guest
Posts: n/a
 
      22nd Jan 2009
Hi,

I'm trying to do some data analysis using Cochran's test for outlying
variances.
I have 4 replicate numbers from each of 20 laboratories. I calculate the
variance of each set of data.
I can work out the Cochran's test value by dividing the maximum variance by
the sum of all the variances.
Then I need to compare this with the Cochran critical values, which are
available from tables, but these have gaps, so I'd like to be able to
calculate them.
Does anyone know of a formula to calculate these?

Dave
 
Reply With Quote
 
 
 
 
Bernie Deitrick
Guest
Posts: n/a
 
      22nd Jan 2009
Dave,

You could try to use linear interpolation:

For a value in A2, with your table in D2:EXXX, and values in D are what should match A2:

=TREND(OFFSET($E$2,MATCH(A2,$D$2:$D$XXX)-1,0,2,1),OFFSET($D$2,MATCH(A2,$D$2:$D$XXX)-1,0,2,1),A2)

HTH,
Bernie
MS Excel MVP


"Dave Curtis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I'm trying to do some data analysis using Cochran's test for outlying
> variances.
> I have 4 replicate numbers from each of 20 laboratories. I calculate the
> variance of each set of data.
> I can work out the Cochran's test value by dividing the maximum variance by
> the sum of all the variances.
> Then I need to compare this with the Cochran critical values, which are
> available from tables, but these have gaps, so I'd like to be able to
> calculate them.
> Does anyone know of a formula to calculate these?
>
> Dave



 
Reply With Quote
 
 
 
 
Lori Miller
Guest
Posts: n/a
 
      23rd Jan 2009
it doesn't look like there's a simple formula for small samples, although it
approaches a Chi squared for larger ones (cf.
http://www.watpon.com/table/cochran.pdf).
Somewhat more accurate than a linear approximation would be to use cubic
interpolation around the neighbouring points eg for k=50 and v=1:

=TREND(B15:B18,A15:A18^{1,2,3},A25^{1,2,3})

gives 0.2599 as opposed to 0.2461 for the linear case. This formula can be
generalised by adapting Bernie's formula above (using offset(...-2,0,4,1) and
^{1,2,3}).


"Dave Curtis" wrote:

> Hi,
>
> I'm trying to do some data analysis using Cochran's test for outlying
> variances.
> I have 4 replicate numbers from each of 20 laboratories. I calculate the
> variance of each set of data.
> I can work out the Cochran's test value by dividing the maximum variance by
> the sum of all the variances.
> Then I need to compare this with the Cochran critical values, which are
> available from tables, but these have gaps, so I'd like to be able to
> calculate them.
> Does anyone know of a formula to calculate these?
>
> Dave

 
Reply With Quote
 
Dave Curtis
Guest
Posts: n/a
 
      23rd Jan 2009
Thanks for the info.
I was hoping to be able to replicate Cochran's values with a formula, but
I've been unable to ascertain how they were derived.
Lori, your idea of a cubic interpolation seems a good one. I've only done
linear interpolations before. However, using your formula, I get a value of
0.2461, instead of the 0.2599 you obtain. Which bracketing points are best
for a cubic interpolation?
I'm not a statistician, so I'm groping in the dark a little here.

Thanks

Dave

"Lori Miller" wrote:

> it doesn't look like there's a simple formula for small samples, although it
> approaches a Chi squared for larger ones (cf.
> http://www.watpon.com/table/cochran.pdf).
> Somewhat more accurate than a linear approximation would be to use cubic
> interpolation around the neighbouring points eg for k=50 and v=1:
>
> =TREND(B15:B18,A15:A18^{1,2,3},A25^{1,2,3})
>
> gives 0.2599 as opposed to 0.2461 for the linear case. This formula can be
> generalised by adapting Bernie's formula above (using offset(...-2,0,4,1) and
> ^{1,2,3}).
>
>
> "Dave Curtis" wrote:
>
> > Hi,
> >
> > I'm trying to do some data analysis using Cochran's test for outlying
> > variances.
> > I have 4 replicate numbers from each of 20 laboratories. I calculate the
> > variance of each set of data.
> > I can work out the Cochran's test value by dividing the maximum variance by
> > the sum of all the variances.
> > Then I need to compare this with the Cochran critical values, which are
> > available from tables, but these have gaps, so I'd like to be able to
> > calculate them.
> > Does anyone know of a formula to calculate these?
> >
> > Dave

 
Reply With Quote
 
Lori
Guest
Posts: n/a
 
      24th Jan 2009
Dave, i think you're right - it was a typo. It's best to use the neighbouring
points for this ie between the interval BC use the points ABCD, at the
endpoints you can use the two before or after.

"Dave Curtis" wrote:

> Thanks for the info.
> I was hoping to be able to replicate Cochran's values with a formula, but
> I've been unable to ascertain how they were derived.
> Lori, your idea of a cubic interpolation seems a good one. I've only done
> linear interpolations before. However, using your formula, I get a value of
> 0.2461, instead of the 0.2599 you obtain. Which bracketing points are best
> for a cubic interpolation?
> I'm not a statistician, so I'm groping in the dark a little here.
>
> Thanks
>
> Dave
>
> "Lori Miller" wrote:
>
> > it doesn't look like there's a simple formula for small samples, although it
> > approaches a Chi squared for larger ones (cf.
> > http://www.watpon.com/table/cochran.pdf).
> > Somewhat more accurate than a linear approximation would be to use cubic
> > interpolation around the neighbouring points eg for k=50 and v=1:
> >
> > =TREND(B15:B18,A15:A18^{1,2,3},A25^{1,2,3})
> >
> > gives 0.2599 as opposed to 0.2461 for the linear case. This formula can be
> > generalised by adapting Bernie's formula above (using offset(...-2,0,4,1) and
> > ^{1,2,3}).
> >
> >
> > "Dave Curtis" wrote:
> >
> > > Hi,
> > >
> > > I'm trying to do some data analysis using Cochran's test for outlying
> > > variances.
> > > I have 4 replicate numbers from each of 20 laboratories. I calculate the
> > > variance of each set of data.
> > > I can work out the Cochran's test value by dividing the maximum variance by
> > > the sum of all the variances.
> > > Then I need to compare this with the Cochran critical values, which are
> > > available from tables, but these have gaps, so I'd like to be able to
> > > calculate them.
> > > Does anyone know of a formula to calculate these?
> > >
> > > Dave

 
Reply With Quote
 
Lou Janke
Guest
Posts: n/a
 
      28th Mar 2010
Dave

I have devlopped a spreadsheet that includes a macro that will claculate the Cochran value for any combination of sets and dgrees of freedom.

I used Cochran's original paper (1941) to test it and also tested it against published tables.

If you want a copy of the spreadshhet leet me know.

Lou



DaveCurti wrote:

Formula for Cochran's Critical Values
22-Jan-09

Hi

I'm trying to do some data analysis using Cochran's test for outlying
variances
I have 4 replicate numbers from each of 20 laboratories. I calculate the
variance of each set of data
I can work out the Cochran's test value by dividing the maximum variance by
the sum of all the variances
Then I need to compare this with the Cochran critical values, which are
available from tables, but these have gaps, so I'd like to be able to
calculate them.
Does anyone know of a formula to calculate these

Dave

Previous Posts In This Thread:

On Thursday, January 22, 2009 11:08 AM
DaveCurti wrote:

Formula for Cochran's Critical Values
Hi

I'm trying to do some data analysis using Cochran's test for outlying
variances
I have 4 replicate numbers from each of 20 laboratories. I calculate the
variance of each set of data
I can work out the Cochran's test value by dividing the maximum variance by
the sum of all the variances
Then I need to compare this with the Cochran critical values, which are
available from tables, but these have gaps, so I'd like to be able to
calculate them.
Does anyone know of a formula to calculate these

Dave

On Thursday, January 22, 2009 6:33 PM
LoriMille wrote:

it doesn't look like there's a simple formula for small samples, although it
it doesn't look like there's a simple formula for small samples, although it
approaches a Chi squared for larger ones (cf.
http://www.watpon.com/table/cochran.pdf).
Somewhat more accurate than a linear approximation would be to use cubic
interpolation around the neighbouring points eg for k=50 and v=1

=TREND(B15:B18,A15:A18^{1,2,3},A25^{1,2,3}

gives 0.2599 as opposed to 0.2461 for the linear case. This formula can be
generalised by adapting Bernie's formula above (using offset(...-2,0,4,1) and
^{1,2,3})

"Dave Curtis" wrote:

On Friday, January 23, 2009 4:01 AM
DaveCurti wrote:

Thanks for the info.
Thanks for the info
I was hoping to be able to replicate Cochran's values with a formula, but
I've been unable to ascertain how they were derived
Lori, your idea of a cubic interpolation seems a good one. I've only done
linear interpolations before. However, using your formula, I get a value of
0.2461, instead of the 0.2599 you obtain. Which bracketing points are best
for a cubic interpolation
I'm not a statistician, so I'm groping in the dark a little here

Thank

Dav

"Lori Miller" wrote:

On Friday, January 23, 2009 9:12 PM
Lor wrote:

Dave, i think you're right - it was a typo.
Dave, i think you're right - it was a typo. It's best to use the neighbouring
points for this ie between the interval BC use the points ABCD, at the
endpoints you can use the two before or after

"Dave Curtis" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Configure and Send SMTP Mail Based on Message Within an Orchestration
http://www.eggheadcafe.com/tutorials...e-and-sen.aspx
 
Reply With Quote
 
CellShocked
Guest
Posts: n/a
 
      28th Mar 2010
On Sun, 28 Mar 2010 06:37:43 -0700, Lou Janke wrote:

>Dave
>
>I have devlopped a spreadsheet that includes a macro that will claculate the Cochran value for any combination of sets and dgrees of freedom.
>
>I used Cochran's original paper (1941) to test it and also tested it against published tables.
>
>If you want a copy of the spreadshhet leet me know.
>
>Lou


You could post it onto a free hosting site like 'mediafire' or the
like, then post the link here.

OR, you could post it as a template on the Microsoft template site.
That would only be if it is macro free, or if you put all the macros into
a worksheet as text, allowing the user to apply them into the VBeditor
manually to get the workbook to function.
 
Reply With Quote
 
New Member
Join Date: Jul 2010
Posts: 2
 
      10th Aug 2010
Maybe you find this article helpful:

R.U.E. 't Lam, "Scrutiny of variance results for outliers: Cochrans test optimized", Analytica Chimica Acta 659 (2010) 68-84.
Reprints available through http://www.pcreview.co.uk/forums/(E-Mail Removed)

In this article I develop an equation (Eq. 28) that will calculate accurate critical values Cc for the traditional Cochran's C test using Excel. The equation requires critical F values Fc as input parameter. Fc can be obtained from Excel function FINV: FINV[significance level, degrees of freedom 1, degrees of freedom 2].

Cc(alpha1,n,L) = 1 / {1 + (L-1) / FINV[alpha1/L, (n-1), (L-1)(n-1)]}

Where
alpha1 = one-sided significance level
L = total number of data series
n = total number of replicates in a single data series

Equation 28 works for any significance level 0 <= alpha1 <= 1, any number of data series L >= 2, and any number of replicates per data series n >= 2.


Last edited by rtlam; 16th Aug 2010 at 11:31 PM..
 
Reply With Quote
 
New Member
Join Date: Jul 2010
Posts: 2
 
      6th Dec 2010
I have started a blog with
- Short introduction to the Variance Outlier Test (G test)
- Additional explalantion
- My updated manuscript
- More extensives tables with critical values

http://www.rtlam.blogspot.com/
 
Reply With Quote
 
rtlam@zeelandnet.nl
Guest
Posts: n/a
 
      8th Feb 2013
On Thursday, January 22, 2009 5:08:11 PM UTC+1, Dave Curtis wrote:
> Hi,
>
> I'm trying to do some data analysis using Cochran's test for outlying
> variances.
> I have 4 replicate numbers from each of 20 laboratories. I calculate the
> variance of each set of data.
> I can work out the Cochran's test value by dividing the maximum variance by
> the sum of all the variances.
> Then I need to compare this with the Cochran critical values, which are
> available from tables, but these have gaps, so I'd like to be able to
> calculate them.
> Does anyone know of a formula to calculate these?
>
> Dave


A formula to calculate critical values for Cochranís C test can be found in:

R.U.E. ít Lam, "Scrutiny of variance results for outliers: Cochranís test optimized", Analytica Chimica Acta 659 (2010) 68Ė84. Equation 28 calculates the exact critical values for the traditional Cochran's C test. Theformula works for any number of data sets, any number of replicates per data set, and at any confidence level. The equation requires critical F as input parameter. Critical F is obtained from Excel function FINV.

To make the contents of the article readily available, and to give further directions on how to perform this variance outlier test, I maintain a blog:
http://rtlam.blogspot.com/

Regards,

Ruben 't Lam
 
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
Find matching values, copy/paste values as well as values in ColA ryguy7272 Microsoft Excel Programming 2 28th Sep 2009 06:20 AM
Critical Counitif formula Christopher Naveen Microsoft Excel Worksheet Functions 3 2nd Mar 2009 05:08 PM
Calculating values to column D with formula based on values column A spolk Microsoft Excel Programming 1 30th Apr 2004 06:29 PM
How to Convert Numeric Values into Text Values with a Formula Vijay Microsoft Excel Worksheet Functions 3 10th Nov 2003 04:12 PM
Predict Y-values on new X-values based on other actual X and Y values? NorTor Microsoft Excel Programming 2 10th Aug 2003 03:08 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:05 AM.