Multex Digital Liana Bonnet is a production quality control engineer for Multex Digital, a manufacturer of computer components. Part of Liana’s job is to analyze batches of semiconductor wafers to ensure that the wafers are within design specifications. Wafers have to have a thickness of around 625 microns but that thickness will vary due to the inherit inaccuracy of the machines creating the wafers. Liana needs to ensure that machines are operating correctly because a machine that is constantly creating wafers that are too thin or too thick will have to be removed from the assembly line and retuned.

Liana will suspect a machine is out-of-alignment when the average wafer size of batch of wafers taken from that machine that operates beyond quality control limits. Control limits are established according to the following two equations:

where Xbar is the average wafer thickness from all sample batches, Rbar is the average range of wafer thickness from all sample batches, and A2 is a constant that depends on the batch sample size.

Liana has recorded data from 50 machine batches with the sample size of each batch varying from 3 to 10 wafers. Liana wants you to report which of the 50 machines are no longer operating within control limits. Complete the following:

1. 1.Open the NP_EX_3-3.xlsx workbook located in the Excel3 > Case1 folder included with your Data Files, and then save the workbook as NP_EX_3_Quality in the location specified by your instructor.
2. 2.In the Documentation sheet, enter your name in cell B3. Use an Excel function to display the current date in cell B4.
3. 3.In the Control Data worksheet, in the range A6:A55, use AutoFill to enter the text strings Batch-1 through Batch-50. In the range B5:K5, use AutoFill to enter the text strings Wafer-1 through Wafer-10.
4. 4.Enter the following summary statistics to the worksheet:
   1. In cell M6, use the COUNT function to the count of number of values in the range B6:K6.
   2. In cell N6, calculate the difference between the maximum value in the range B6:K6 (using the MAX function) and the minimum value in the range B6:K6 (using the MIN function).
   3. In cell O6, use the AVERAGE function to calculate the average wafer size in the range B6:K6.
   4. Use AutoFill to extend the formulas in the range M6:O6 through the range M6:O55.
5. 5.Calculate the following quality control statistics:
   1. In cell V5, display the value of Xbar by using the AVERAGE function to calculate the average of the values in column O.
   2. In cell V6, display the value of Rbar by using the AVERAGE function to calculate the average of the values in column N.
6. 6.Do the following to complete a lookup table that you will use to calculate the lower and upper control limits for batch samples sizes of 2 up to 25:
   1. In cell W10, calculate the lower control limit by returning the value of cell V5 minus the value of cell V10 times cell V6. Use absolute references for cells V5 and V6 and a relative reference for cell V10. Check your formula by verifying that cell W10 shows the value 598.45.
   2. In cell X10, calculate the upper control limit by returning the value of cell V5 plus the value of cell V10 times cell V6. Once again, use absolute references for cells V5 and V6 and a relative reference for cell V10. Check your formula by verify that cell X10 shows the value 651.61.
   3. Use AutoFill to extend the formulas in the range W10:X10 over the range W10:X33 to show the lower and upper control limits for batch sizes ranging from 2 up to 25.
7. 7.In cell P6, use the VLOOKUP function to display the lower control limit for the first batch from the assembly line using cell M6 as the lookup value, the range $U$9:$X$33 as the lookup table, 3 as the column index number, and FALSE for the range_lookup value. Extend the formula in cell P6 over the range P6:P55.
8. 8.Repeat Step 7 in cell Q6 using 4 as the column index number in the VLOOKUP function to retrieve the upper control limit for the first batch and then extend the formula over the range Q6:Q55.
9. 9.Determine whether a batch is not in control because the batch average falls below the lower control limit. In cell R6, use an IF function to test whether the value of sample average in cell O6 is less than the value of lower control limit in cell P6. If the condition is true, display “Out of Control”; otherwise, display “In Control” in the cell. Extend the formula over the range R6:R55 to indicate which batches are falling below the lower control limit for the machinery.
10. 10.Repeat Step 9 for cell S6 except test for the condition that sample average in cell O6 is greater than the value of the upper control limit in cell Q6. Extend the formula over the range S6:S55 to indicate which batches are operating above the upper control limit.
11. 11.Add conditional formatting to the range R6:S55, displaying any cell containing the text “Out of Control” in a red font on a light red background.
12. 12.In cell A58, write your conclusions indicating which of the 50 machines on the assembly line are not within the control parameters set by Liana and indicate in what ways those machines are failing.
13. 13.Save the workbook, and then close it.