Formula for Cochran's Critical Values

Discussion in 'Microsoft Excel Worksheet Functions' started by Dave Curtis, Jan 22, 2009.

  1. Dave Curtis

    Dave Curtis Guest

    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
     
    Dave Curtis, Jan 22, 2009
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    > 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
     
    Bernie Deitrick, Jan 22, 2009
    #2
    1. Advertisements

  3. Dave Curtis

    Lori Miller Guest

    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
     
    Lori Miller, Jan 22, 2009
    #3
  4. Dave Curtis

    Dave Curtis Guest

    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
     
    Dave Curtis, Jan 23, 2009
    #4
  5. Dave Curtis

    Lori Guest

    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
     
    Lori, Jan 24, 2009
    #5
  6. Dave Curtis

    Lou Janke Guest

    Cochran's Values

    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/tutorial...1-a2f309a021c2/biztalk-configure-and-sen.aspx
     
    Lou Janke, Mar 28, 2010
    #6
  7. Dave Curtis

    CellShocked Guest

    Re: Cochran's Values

    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.
     
    CellShocked, Mar 28, 2010
    #7
  8. Dave Curtis

    rtlam

    Joined:
    Jul 22, 2010
    Messages:
    2
    Likes Received:
    0
    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

    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: Aug 16, 2010
    rtlam, Aug 10, 2010
    #8
  9. Dave Curtis

    rtlam

    Joined:
    Jul 22, 2010
    Messages:
    2
    Likes Received:
    0
    Blog "Variance Outlier Test"

    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/
     
    rtlam, Dec 5, 2010
    #9
  10. Dave Curtis

    Guest

    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
     
    , Feb 8, 2013
    #10
    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. Vijay

    How to Convert Numeric Values into Text Values with a Formula

    Vijay, Nov 10, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    349
    Peo Sjoblom
    Nov 10, 2003
  2. ramana
    Replies:
    1
    Views:
    245
    Dave Peterson
    Oct 5, 2005
  3. Guest

    Build excel formula using field values as text in the formula

    Guest, Apr 18, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    773
    Guest
    Apr 18, 2007
  4. Dan G

    How can I display formula values instead of the formula?

    Dan G, Sep 18, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    406
    Bernard
    Sep 24, 2008
  5. Christopher Naveen

    Critical Counitif formula

    Christopher Naveen, Mar 2, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    194
    Shane Devenshire
    Mar 2, 2009
Loading...

Share This Page