# 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.