Module 3 (Computer Applications

.docx

School

Sheridan College *

*We aren’t endorsed by this school

Course

COMPUTER S

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by ChefPantherMaster56

Module 3: Multex Digital Case Problem 1: 1. Open the NP_EX_3-3 workbook file 2. In the documentation sheet, enter you name in cell B3. Use the Excel function to display the current date in Cell B4  To do this step. o There are 2 ways to enter date in the cell  You can click the cell and enter the formula “=Today()”  Click the formulas Tab select Date and Time choose today function to display current date. 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:K55, use Autofill to enter the text string Wafer-1 through Wafer-10  To do this step o Select the control Data worksheet o Click cell A^ and type “Batch-1” o Select Cell range A7:A55 > AutoFill using fill handle till A55 to fill the series. 4. Enter the following summary statistics to the worksheet a) In cell M6, use the COUNT function to the count of number of values in the range B6:K6. o To do this step  Select cell M6 > Click Formulas TAB  Click AutoSum option and click count to count the number of values in cell range B6:K6 b) 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 functions). o Select cell N6 > Type the formula to calculate difference between Maximum and Minimum values in the range B6:K6 > Type “=MAX(B6:K6)-MIN (B6:K6) c) In cell O6, use the AVERAGE function to calculate the average wafer size in the range B6:K6 o Select Cell O6 o Click Formulas tab o Click AutoSum and click Average to calculate average for the cell range B6:K6 d) Use Autofill to extend the formulas in the range M6:O6 throught the range M6:O55 o Select Cell range M6 to O6 o Atuofill handle to Autofill formulas till O55 5. Calculate the following quality control statistics

a) In cell V5, display the value of Xbar by using the AVERAGE function to calculate the average of the values in column O. b) In cell V6, display the value of Rbar by using the AVERAGE function to calculate the average of the values in column N. o To do this Step o A) Select cell V5 > Click formulas tab  Select AutoSum and click AVERAGE > In average function  Select cell range O6:O55 to calculate average for values in column O o B) Select V6 > Click formulas Tab o Select AutoSum and lcick AVERAGE o In the average function, select cell range N6:N55 to calculate average for values In column N > Press Enter. 6. Do the following to complete a lookup table that you will use the calculate the lower and upper control limits for batch samples sizes of 2 up to 25: a) 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  To do this Step o A) Select cell W10 > Type “=$V$5 – ($V$6*V10) $V$5 and $V$6 refers the absolute reference values an dV10 is relative value. Using the formula “=V5-(V10*V6) Lower control limit can be calculated. o Lower control value for W10 will be 598.45 b) In cell X10, calculate the upper control limit by returning the value of cell V5 plus the value for 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  To do This Step o Select cell X10 > Type the formula “=$V$5+($V$6*V10)” to calculate upper control limit. o The value for X10 cell will be 651.61 c) Use AutoFill to extend the formulas in the range W10:X110 over the range W10:X33 to show the lower and upper control limits for batch sixes ranging from 2 up to 25.  To do this Step o Select cell W10:X10. Drag it down till X33 To AutoFill formula using the fill handle. 7. In cell P6, use the VLOOKUP function to display the lower control limit for the first batch from 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_Look value. Extend the formula in cell P6 over the range P6:P55

 To do this step o Select cell P6 > Using VLOOKUP function, o Select M6 as lookup value, $U$9:$X$33 as look table values, column index number as 3 o Range lookup value as FALSE > By combining these values, in cell P6 type “=VLOOKUP(M6, $U$9:$X$33, 3, FALSE)” o Using the fill handle AutoFill the formula till P55 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  To do this step o Select cell Q6 > Type “=VLOOKUP(M6, $U$9 : $X$33, 4, FALSE) to retrieve upper control value 9. Determien 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 failing below the lower control limit for the machinery.  To do this Step o Select Cell R6 > Click formula tab o Select logical and click IF function the dialog box appears  In the dialog box type - Logical_Test: O6:P6 = False - Value_IF_True “Out of control” - Value_IF_False “In control” o Using fill handle Autofill R55. 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 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.  To do this step o Select cell S6 > click formula’s tab o Select logical and click IF function o Type “O6 > Q6” in logical test box  Type “Out of control” in value if true box  Type “in control” in value if false box o AutoFill the formula in S6 till S55 11. Add conditional formatting to the range R6:R55, displaying any cell containing the text “Out of control” in Red font on a light red background  To do this Step o Select cell Range R6:R55 > Click Home tab o Select conditional formatting (can be found in the home tab)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help