iSixSigma

d2 Function

Six Sigma – iSixSigma Forums Old Forums General d2 Function

This topic contains 6 replies, has 5 voices, and was last updated by  MBBinWI 9 years, 9 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #53115

    Bill McNeese
    Participant

    I have seen a number of people ask for a spreadsheet that can be used to calculate the value of d2 up to n = 100. You can easily do this using the following function I developed for use in Excel for any value of n. The code for this function is given below. To use the function, do the following:1. Copy the code below
    2. Open up a new workbook in Excel
    3. Open VBA (Press Alt + F11)
    4. Insert a new module (Insert > Module)
    5. Paste the Excel user defined function examples –
    6. Close VBA (Press Alt+Q)
    7. To use the function, enter the following into any cell: = d2cal(n), where n is an integer. The value of d2 will be returned. Module)
    5. Paste the Excel user defined function examples –
    6. Close VBA (Press Alt+Q)
    7. To use the function, enter the following into any cell: = d2cal(n), where n is an integer. The value of d2 will be returned. Module)
    5. Paste the Excel user defined function examples –
    6. Close VBA (Press Alt+Q)
    7. To use the function, enter the following into any cell: = d2cal(n), where n is an integer. The value of d2 will be returned.Bill*********************************
    Function d2cal(n)
    Dim StartNum As Long, EndNum As Long, NumPoints As Long, Dx As Double
    Dim Int1 As Double, Int2 As Double, i As Long
    StartNum = -200
    EndNum = 200
    NumPoints = 1000
    Dx = (EndNum – StartNum) / NumPoints
    Int1 = 1 – (1 – Application.NormSDist(StartNum)) ^ n – (Application.NormSDist(StartNum)) ^ n
    For i = 1 To (NumPoints – 1)
    Int2 = 1 – (1 – Application.NormSDist(StartNum + (Dx * i))) ^ n – (Application.NormSDist(StartNum + (Dx * i))) ^ n
    Int1 = Int1 + 2 * Int2
    Next i
    Int2 = 1 – (1 – Application.NormSDist(EndNum)) ^ n – (Application.NormSDist(EndNum)) ^ n
    Int1 = Int1 + Int2
    d2cal = (Dx / 2) * Int1
    End Function

    0
    #188099

    lin
    Participant

    Let’s try that code again:Function d2cal(n)Dim StartNum As Long, EndNum As Long, NumPoints As Long, Dx As DoubleDim Int1 As Double, Int2 As Double, i As Long StartNum = -200 EndNum = 200 NumPoints = 1000 Dx = (EndNum – StartNum) / NumPoints Int1 = 1 – (1 – Application.NormSDist(StartNum)) ^ n – (Application.NormSDist(StartNum)) ^ n For i = 1 To (NumPoints – 1) Int2 = 1 – (1 – Application.NormSDist(StartNum + (Dx * i))) ^ n – (Application.NormSDist(StartNum + (Dx * i))) ^ n Int1 = Int1 + 2 * Int2
    Next i Int2 = 1 – (1 – Application.NormSDist(EndNum)) ^ n – (Application.NormSDist(EndNum)) ^ n Int1 = Int1 + Int2 d2cal = (Dx / 2) * Int1End Function

    0
    #188100

    Bill McNeese
    Participant

    Sorry, the paste didn’t work quite correctly before.Function d2cal(n)Dim StartNum As Long, EndNum As Long, NumPoints As Long, Dx As DoubleDim Int1 As Double, Int2 As Double, i As LongStartNum = -200EndNum = 200NumPoints = 1000Dx = (EndNum – StartNum) / NumPointsInt1 = 1 – (1 – Application.NormSDist(StartNum)) ^ n – (Application.NormSDist(StartNum)) ^ nFor i = 1 To (NumPoints – 1)Int2 = 1 – (1 – Application.NormSDist(StartNum + (Dx * i))) ^ n – (Application.NormSDist(StartNum + (Dx * i))) ^ nInt1 = Int1 + 2 * Int2 Next iInt2 = 1 – (1 – Application.NormSDist(EndNum)) ^ n – (Application.NormSDist(EndNum)) ^ nInt1 = Int1 + Int2d2cal = (Dx / 2) * Int1End Function

    0
    #188153

    Bower Chiel
    Participant

    Hi Bill
    Well done – a much neater approach than mine!  I’ve been offering people a copy of my Excel spreadsheet for calculation of d2 and there has been quite a lot of interest in it.  It works for any value of n but a mathematician friend calculated d2 for n = 2 to 100 using Mathcad and our results agree.  I’ve set up your function and all three of us agree up to n = 100.  Out of interest I compared the results from your function for n = 200 in steps of 100 up to n = 1000 and we again agree.  I’d be interested to hear how you do the computation within your function.
    However I can’t help but wonder if we are in fact doing a disservice and leading people to use range methods with sample sizes for which use of standard deviation would be much more efficient in the technical statistical sense.  Even Shewhart commented on this on p. 297 of his 1931 book stating that “the very rapid decrease in the efficiency of the estimate (of sigma) derived from the range is striking”!  In fact one can argue that unless the sample size is very small then one should not range-based methods to estimate process sigma.
    Best Wishes
    Bower Chiel
     
      

    0
    #188174

    Bill McNeese
    Participant

    I agree with you on the use of range. In fact, with today’s software, there is really no reason that a Xbar-s chart should not be used in place of the Xbar-R chart at all times. I guess it is the old paradigm that it has always been done that way and, of course, the range is easier to understand for most.Best Regards,Bill

    0
    #188212

    DFSS MBB
    Participant

    Bill,
    Can you e-mail your excel worksheet?
    Thanks

    0
    #188228

    MBBinWI
    Participant

    I’m sorry to have to post this to someone identifying themselves as a DfSS MBB, as that is my passion and I believe we are a select few, but when it’s called for, it’s called for.
    The spreadsheet is unable to be electronically communicated.  Please tie a red bandana to your mailbox and the author will drop off a print out for you.

    0
Viewing 7 posts - 1 through 7 (of 7 total)

The forum ‘General’ is closed to new topics and replies.