# Excel - Formula for Cochran's Critical Values

Asked By DaveCurti on 22-Jan-09 11:08 AM
```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```

LoriMille replied on 22-Jan-09 06:33 PM
```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}).```
DaveCurti replied on 23-Jan-09 04:01 AM
```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```
Lor replied on 23-Jan-09 09:12 PM
```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.```
Lou Janke replied to DaveCurti on 28-Mar-10 09:37 AM
```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```
CellShocked replied to Lou Janke on 28-Mar-10 01:41 PM
```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.```