How Do I Create X-Bar & Range Control Charts in Excel Using SigmaXL?

1. Open the file Catapult Data – Xbar Control Charts.xlsx. Each operator fires the ball 3 times. The target distance is 100 inches. The Upper Specification Limit (USL) is 108 inches. The Lower Specification Limit (LSL) is 92 inches.
2. Select B2:F22; here, we will only use the first 20 subgroups to determine the control limits.
3. Select SigmaXL > Control Charts > X-Bar & R. Do not check Use Entire Data Table!
   
   
   
   
4. Click Next. Select Subgroups across Rows, select Shot 1, Shot 2, Shot 3, click Numeric Data Variables (Y) >>; select Operator, click Optional X-Axis Labels >>. Check Tests for Special Causes as shown:






5. Click OK. Resulting X-bar & R charts:
   
   
   
   
6. This is currently a stable catapult process. Subgroups 21 to 25 were added afterwards. To add the additional data to this chart, click SigmaXL Chart Tools > Add Data to this Control Chart as shown.
   
   
   
   
7. Note that the Add Data button does NOT recalculate the control limits. Once control limits are established, they should only be recalculated when a deliberate process change or improvement is introduced.
8. The Tests for Special Causes report gives us more detail on the recent instability:
   
   
   
   The X-bar chart and Tests for Special Causes report clearly shows that this process is now out of control with an unstable mean. The process must be stopped, and the Out-of-Control Action Plan must be followed to determine and fix the root cause. In this case, the assignable cause was a change of rubber band requiring a reset of the pull back angle. The use of tests for special causes gave us an early warning of this at observation number 22.
   
   Note that the Range chart is in-control even though the X-Bar chart is out-of-control:
   
   
   
   
9. The tests for special causes can have defaults set to apply any or all of Tests 1-8. Test 2 can be set to 7, 8, or 9 points in a row on same side of CL. Click SigmaXL > Control Charts > “Tests for Special Causes” Defaults to run selected tests for special causes. (Note that these defaults will apply to Individuals and X-bar charts. Test 1 to 4 settings will be applied to Attribute Charts.)
10. To add a comment to a data point, select SigmaXL Chart Tools > Add Data Label. Select Text Label. Enter a comment as shown. Click on the data point to add the comment. Click Done.
    
    
    
    
11. Now we will look at Process Capability Indices for this process. Click on Sheet 1 (or press F4 to activate last worksheet). Click SigmaXL > Control Charts > X-Bar & R. Check Use Entire Data Table. Click Next. (Alternatively select B2:F27, press F3.)
12. Select Shots 1-3, click Numeric Data Variables (Y) >>.
13. Click Advanced Options. Enter LSL = 92, Target = 100, USL = 108.
14. The resulting dialog box settings are shown:
    
    
    
    
15. Click OK. Click X-Bar & R – Proc Cap sheet for the Process Capability report:
    
    
    
    Note the difference between Pp and Cp; Ppk and Cpk. This is due to the process instability. If the process was stable, the actual performance indices Pp and Ppk would be closer to the Cp and Cpk values.

X-Bar & R Charts – Exclude Subgroups

16. Click on Sheet 1 (or press F4 to activate last worksheet). Click SigmaXL > Control Charts > X-Bar & R. Check Use Entire Data Table. Click Next.
17. Select Shots 1-3, click Numeric Data Variables (Y) >>. Ensure that Calculate Limits is selected. Click OK.
18. The resulting X-bar & R charts are displayed:



The control limits here were calculated including subgroups 21 to 25 which have a known assignable cause.


19. To calculate the control limits excluding subgroups 21 to 25, click SigmaXL Chart Tools > Exclude Subgroups. Select Show Highlighted Points for Excluded Subgroups. Enter 21,22,23,24,25 as shown:
    
    
    
    
20. Click Exclude Subgroups. The control chart limits are recalculated and the excluded points are highlighted: