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

- AuthorPosts
- January 7, 2010 at 8:59 pm #53115

Bill McNeeseParticipant@Bill-McNeese**Include @Bill-McNeese in your post and this person will**

be notified via email.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 Function0January 7, 2010 at 9:01 pm #188099Let’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 Function0January 7, 2010 at 9:05 pm #188100

Bill McNeeseParticipant@Bill-McNeese**Include @Bill-McNeese in your post and this person will**

be notified via email.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

0January 9, 2010 at 4:52 pm #188153

Bower ChielParticipant@Bower-Chiel**Include @Bower-Chiel in your post and this person will**

be notified via email.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

0January 10, 2010 at 1:26 pm #188174

Bill McNeeseParticipant@Bill-McNeese**Include @Bill-McNeese in your post and this person will**

be notified via email.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

0January 11, 2010 at 1:44 pm #188212

DFSS MBBParticipant@DFSS-MBB**Include @DFSS-MBB in your post and this person will**

be notified via email.Bill,

Can you e-mail your excel worksheet?

Thanks0January 11, 2010 at 10:03 pm #188228

MBBinWIParticipant@MBBinWI**Include @MBBinWI in your post and this person will**

be notified via email.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 - AuthorPosts

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