LAB 8 INSTRUCTIONS FOR EXCEL 2007 XBAR AND RANGE CONTROL CHARTS FOR PROCESS WITH SIGMA UNKNOWN NAME LAB TIME LAB BLDG 1 2 3 4 Xbar and Range control charts are used in many process control applications In this lab we will start with data on a process where the customer s desired dimension is 140 146 mm and therefore the target dimension is 143mm For each sample three parts are selected from recent production The diameter of each part is measured and recorded Open the Lab8Data file You will see data for 30 samples of size 3 CALCULATE MEAN AND RANGE FOR EACH SAMPLE 5 In F2 calculate the sample mean for the first sample Copy this calculation down to obtain the sample means for all 30 samples 6 In G2 calculate the sample range for the first sample Enter MAX B2 D2 MIN B2 D2 Copy this calculation down for all 30 samples 7 In F33 calculate the overall mean of the sample means Increase the precision of the result in the following way right click on the cell choose Format Cells under the Number label change Decimal Places to 4 XBARBAR 8 In G33 calculate the overall mean of the sample ranges RBAR CALCULATE CONTROL LIMITS FOR XBAR CHART 9 The UCL TARGET A2 RBAR When n 3 A2 1 023 UCL The CL TARGET CL The LCL TARGET A2 RBAR LCL 10 In I2 enter 143 1 023 G33 In J2 enter 143 In K2 enter 143 1 023 G33 11 Copy the VALUES in I2 K2 Not the formulas down through line 31 using Copy Paste Special Values The values should be the same on every row CALCULATE CONTROL LIMITS FOR RANGE CHART 12 The UCL D4 RBAR When n 3 D4 2 574 UCL The CL RBAR CL The LCL D3 RBAR When n 3 D3 0 LCL 13 In M2 enter 2 574 G33 In N2 enter G33 In O2 enter 0 14 Copy the VALUES in M2 O2 Not the formulas down through line 31 using Copy Paste Special Values The values should be the same on every row CONSTRUCT THE XBAR CHART 15 Hold the Ctrl key down and highlight A1 A31 F1 F31 I1 I31 J1 J31 K1 K31 16 Go to the Chart Wizard select XY Scatter select the type with the points connected by straight lines next next enter title XBAR CHART FOR DIAMETER enter label for X axis SAMPLE NUMBER enter label for Y axis SAMPLE MEAN next and put the chart in the worksheet CONSTRUCT THE RANGE CHART 17 Highlight column A then hold the Ctrl key down and highlight G M N O from row1 through row 31 18 Go to Insert select XY Scatter select the type with the points connected by straight lines Go to layout click on Chart Title select the second one and enter RANGE CHART FOR DIAMETER as the title Go to layout again and label X axis SAMPLE NUMBER and Y axis SAMPLE RANGE 19 Print the XBAR and RANGE charts to turn in with this instruction question sheet 20 Save your file INTERPRET THE CONTROL CHARTS 21 Rule 1 Any point on either chart which is outside the control limits is evidence that the process is affected by Special Cause variation and is out of control 22 Rule 2 A run of 9 consecutive points on the same side of the centerline on any chart is evidence that the process is affected by Special Cause variation and is out of control 23 Rule 3 A trend of 7 consecutive points either ascending or descending on any chart is evidence that the process is affected by Special Cause variation and is out of control 24 List the out of control signals on the lines below Sample Number Chart Type Rule Violated PROCESS CAPABILITY CALCULATION OF Cp AND Cpk 25 26 27 Estimate process standard deviation from formula RBAR d2 For a sample size 3 d2 1 693 Technically we should have an RBAR which does not include any out of control values If we recalculate RBAR for samples 13 through 30 we get RBAR 1 4211 Use this value for the estimate of Estimate of The customer s tolerance for the diameter 140 146 Calculate Cp using the formula Cp cust max cust min 6 Cp 28 Cpk cannot be calculated because there has not been a long run at least 20 samples under stable conditions However lets pretend that the manufacturer has no more outof control conditions and that the XBARBAR since the last out of control signal 142 5 Calculate the value of Cpk using this value of XBARBAR Cpk The distance between XBARBAR and nearest specification limit 3 The numerator should be a positive number so that Cpk comes out Cpk Will this value of Cpk satisfy the customer 29 What would Cpk be if the process mean XBARBAR is raised to 143 0