Control Charts Made Easy - Calculating d2 - How-to Guide for Excel and Minitab

Note: This is the simplified How-to guide

Click here for the full article with background details and history

The table shows d2 factors for n=1,2,3...10. You will notice that the d2 increases with each n, but that the difference between subsequent d2 values decreases.

Excel - How do I calculate d2?

1. Generate a large number of samples (I generated 1,000,000)
2. Create a sample using two columns for which you generate values using the standard random generator for a normal distribution with x-bar (mean) = 0 and s (RMS standard deviation) = 1
3. For each sample, calculate the range r, which is the absolute (positive) difference between the two values for each sample.
4. Copy this down 1,000,000 times (Excel can hold 1,04 million cells), feel free to make the sample larger
5. Calculate the average of all these 1,000,000 ranges, this average approximates the expected value of the range for samples of n=2
6. Since these samples are from a standard normal distribution and the population standard deviation σ is 1, the average range directly provides an estimate of d2.

Detailed steps in Excel

1. Open Excel and set up headers in row 1:

• A1: "Random 1"
• B1: "Random 2"
• C1: "Range"
• D1: "Average Range"

2. Enter the random number formula in cell A2: =NORMINV(RAND(), 0, 1)

Copy this formula down to A1000001

3. Enter the random number formula in cell B2: =NORMINV(RAND(), 0, 1)

Copy this formula down to B1000001

4. Enter the range formula in cell C2: =AVERAGE(C2:C1000001)

Copy this formula down to C1000001.

5. Calculate the average range in cell D1: =AVERAGE(C2:C1000001)

The results

See below a histogram displaying all the ranges of 1,000,000 datapoints, where n=2. In red the average of all these ranges = d2.

Using this Monte-Carlo simulation, I got for n=2; d2 = 1.12832

The published reference for d2 to 6 decimal places is 1.12838, thus the difference between my calculated and the published version is less than 0.006%. I think that is acceptable! ;)

I hope you have fun replicating this experiment!

Excel - distributions for different number of samples in your subgroup

Below are 3 additional histograms for n=3,4,5.

n=3, d2=1.693 (I got 1.694)

n=4, d2 = 2.059 (I got 2.057)

n=5, d2=2.326 (I got d2=2.326!) ;)

Minitab - How do I calculate d2?

1. Use Minitab's random generator to create 10 columns of data using a normal distribution with a mean = 0 and standard deviation = 1.

2. Use the calculator function for n=2...10 to calculate the ranges

3. Finally, use the graphical summaries to assess d2 for each case

Here are the graphical summaries, have a look at the shape, centre and spread, then have a look at the mean value (d2), the p-value for the Anderson-Darling test for normality, the median and then the plots at the bottom. What conclusions do you draw from this?

Minitab - graphical results

If you liked this, read and watch these

If you like articles, webinars, podcasts or vodcasts, listen/watch/read these:

1. Listen: podcast celebrating 100 years of the control chart
2. Watch: vodcast celebrating 100 years of the control chart
3. Watch: a webinar celebrating 100 years of the control chart
4. Read: A timeline of Shewhart's life
5. Read: Celebrating 100 years of the control chart
6. Read: The birth of the Control chart - discovering the Drivers for Shewhart's creation
7. Read: Full article on learning how to calculate d2