EX $3-54$ Excel $|$ Module $3$ Performing Calculations with Formulas and Functions

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

Lower Control Limit $(LCL)=$ Xbar $-A_2$ Rbar

Upper Control Limit $(UCL)=$ Xbar $+A_2$ Rbar

where $xb$ ar is the average wafer thickness from all sample batches, $\frac{R}{b}ar$ is the average range of wafer thickness from all sample batches, and $A_2$ 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:

Open the NP$\_$EX$\_3-3.$xIsx workbook located in the Excel$3>$ Case$1$ folder included with your Data Files, and then save the workbook as NP$\_$EX$\_3\_$Quality in the location specified by your instructor.

In the Documentation sheet, enter your name in cell B$3.$ Use an Excel function to display the current date in cell B$4.$

In the Control Data worksheet, in the range A$6$:A$55,$ use AutoFill to enter the text strings Batch$-1$ through Batch$-50.$ In the range B$5$:K$5,$ use AutoFill to enter the text strings Wafer$-1$ through Wafer$-10.$

Enter the following summary statistics to the worksheet:

a$.$ In cell M$6,$ use the COUNT function to the count of number of values in the range B$6$:K$6.$

b$.$ In cell N$6,$ calculate the difference between the maximum value in the range B$6$:K$6($using the MAX function$)$ and the minimum value in the range B$6$:K$6($using the MIN function$).$

c$.$ In cell O$6,$ use the AVERAGE function to calculate the average wafer size in the range B$6$:K$6.$

d$.$ Use AutoFill to extend the formulas in the range M$6$:O$6$ through the range M$6$:O$55.$

Calculate the following quality control statistics:

a$.$ In cell V$5,$ display the value of Xbar by using the AVERAGE function to calculate the average of the values in column $O.$

b$.$ In cell V$6,$ display the value of Rbar by using the AVERAGE function to calculate the average of the values in column $N.$

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$ :

a$.$ In cell W$10,$ calculate the lower control limit by returning the value of cell V $5$ minus the value of cell V$10$ times cell V$6.$ Use absolute references for cells $V5$ and $V6$ and a relative reference for cell V$10.$ Check your formula by verifying that cell W$10$ shows the value $598\mathrm{.}45.$

b$.$ In cell $x10,$ calculate the upper control limit by returning the value of cell $V5$ plus the value of cell V$10$ times cell V$6.$ Once again, use absolute references for cells V$5$ and V$6$ and a relative reference for cell V$10.$ Check your formula by verify that cell X$10$ shows the value $651\mathrm{.}61.$

c$.$ Use AutoFill to extend the formulas in the range W$10$:X$10$ over the range W$10$:X$33$ to show the lower and upper control limits for batch sizes ranging from $2$ up to $25.$

In cell P$6,$ use the VLOOKUP function to display the lower control limit for the first batch from the assembly line using cell M$6$ as the lookup value, the range $$$$$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 P$6$ over the range P$6$:P$55.$