Formula for Cochran's Critical Values

[Dave Curtis]

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

[@consumerdotorg Bernie Deitrick]

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" <DaveC...@discussions.microsoft.com> wrote in message
news:4BB9BF08-EAC2-438F...@microsoft.com...

[Lori Miller]

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]

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]

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.

[loujanke]

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,

Dave

Previous Posts In This Thread:

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

Formula for Cochran's Critical Values
Hi,

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.

Thanks

Dave

"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/aspnet/4e9e7c92-1279-4fdb-8631-a2f309a021c2/biztalk-configure-and-sen.aspx

[CellShocked]

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.

[rt...@zeelandnet.nl]

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

[franco777...@gmail.com]

I encountered a similar problem. I needed to determine a value beyond the values in the given tables. Discovered a way by plotting the given values on an x/y scatter plot in excel and then added a power trend line and displaying the formula on the graph. Use the formula to extrapolate the critical values. This works well with interpolation as well. You should get a formula close to: y = 4.7301.x^-0.756 with a correlation coefficient of 0.9981. The best formula I derived was y = 4.7494.x^-0.757 resulting in a correlation coefficient of 0.9993.

Hope this helps,
Franco De Andrade