Fluent Essays

Please submit the final excel sheet Week1Lab.xls from the lab after completing all the steps and follow all the instructions explained in the Week 1 lab.pdf except the welcome to excel tutorial step. lab_assignment_rubric.png mlatsubw2.xlsx week_1_lab.pdf Unformatted Attachment Preview StudyID 002 003 004 005 007 009 010 011 014 016 017 018 021 023 024 027 028 029 030 ExpGroup 3 3 3 1 1 3 1 1 3 2 1 1 1 1 2 3 1 3 2 Age Sex 45 45 46 48 63 46 45 53 47 61 49 46 51 56 63 62 47 45 63 2 2 1 2 2 2 1 1 2 2 1 1 2 2 1 2 1 2 1 Diabetes TRUE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE Chol Arthrits SmokNumb FALSE TRUE 15 FALSE TRUE 20 FALSE TRUE 30 FALSE TRUE 6 FALSE TRUE 20 FALSE TRUE 12 TRUE TRUE 30 FALSE TRUE 20 FALSE FALSE 10 FALSE FALSE 5 TRUE TRUE 35 TRUE TRUE 30 FALSE TRUE 5 TRUE TRUE 5 FALSE TRUE 20 TRUE TRUE 12 TRUE FALSE 20 TRUE FALSE 20 FALSE TRUE 20 StudyID 002 002 002 002 003 004 004 004 004 005 005 005 005 007 007 007 007 009 009 009 009 010 011 011 011 011 ExpGroup 3 3 3 3 3 3 3 3 3 1 1 1 1 1 1 1 1 3 3 3 3 1 1 1 1 1 Age Sex 45 45 45 45 45 46 46 46 46 48 48 48 48 63 63 63 63 46 46 46 46 45 53 53 53 53 VisitN 2 2 2 2 2 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 2 3 4 1 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 1 2 3 4 SysBP 162 155 135 115 137 136 145 119 106 125 108 DiasBP 86 88 78 72 96 84 85 74 64 84 74 128 142 120 128 122 104 122 128 124 146 135 125 130 124 88 86 82 81 82 78 87 88 86 91 75 78 82 77 Height 62 62 62 62 63 67 67 67 67 65 64 64 64.5 64 63 63 63 64 64 64 64 67 65 65.5 66 65 Weight 215 221 217 200 226 210 194 189 163 260 265 268 250 145 148 150 168 161 172 158 221 137 136 140 129 BMI 39.31972 40.41701 39.68548 36.57648 40.02973 32.88706 30.38138 29.59835 25.52662 43.26154 45.48218 45.99707 42.24506 25.68279 26.21416 26.56841 28.83398 27.63257 29.52051 27.11768 34.60971 22.7955 22.28495 22.59412 21.46438 Code Product Name NWTB-1 NWTCO-3 NWTCO-4 NWTO-5 NWTJP-6 NWTJP-6 NWTDFN-7 NWTS-8 NWTDFN-14 NWTCFV-17 NWTBGM-19 NWTBGM-21 NWTB-34 NWTCM-40 NWTSO-41 NWTB-34 NWTCA-48 NWTDFN-51 NWTG-52 NWTP-56 NWTP-57 NWTS-65 NWTS-66 NWTD-72 NWTDFN-74 NWTCO-77 NWTDFN-80 NWTB-81 NWTCS-83 NWTBGM-85 NWTBGM-86 NWTB-87 NWTCFV-88 NWTCFV-89 Northwind Traders Chai Northwind Traders Syrup Northwind Traders Cajun Seasoning Northwind Traders Olive Oil Northwind Traders Boysenberry Spread Northwind Traders Marmalade Northwind Traders Dried Pears Northwind Traders Curry Sauce Northwind Traders Walnuts Northwind Traders Fruit Cocktail Northwind Traders Chocolate Biscuits Mix Northwind Traders Scones Northwind Traders Beer Northwind Traders Crab Meat Northwind Traders Clam Chowder Northwind Traders Coffee Northwind Traders Chocolate Northwind Traders Dried Apples Northwind Traders Long Grain Rice Northwind Traders Gnocchi Northwind Traders Ravioli Northwind Traders Hot Pepper Sauce Northwind Traders Tomato Sauce Northwind Traders Mozzarella Northwind Traders Almonds Northwind Traders Mustard Northwind Traders Dried Plums Northwind Traders Green Tea Northwind Traders Potato Chips Northwind Traders Brownie Mix Northwind Traders Cake Mix Northwind Traders Tea Northwind Traders Pears Northwind Traders Peaches Cost $13.50 $7.50 $16.50 $16.01 $18.75 $60.75 $22.50 $30.00 $17.44 $29.25 $6.90 $7.50 $10.50 $13.80 $7.24 $34.50 $9.56 $39.75 $5.25 $28.50 $14.63 $15.79 $12.75 $26.10 $7.50 $9.75 $3.00 $2.00 $0.00 $9.00 $10.50 $2.00 $0.00 $0.00 NWTCFV-90 NWTCFV-91 NWTCFV-92 NWTCFV-93 NWTCFV-94 NWTCM-95 NWTCM-96 NWTC-82 NWTC-82 NWTSO-98 NWTSO-99 Total Northwind Traders Pineapple Northwind Traders Cherry Pie Filling Northwind Traders Green Beans Northwind Traders Corn Northwind Traders Peas Northwind Traders Tuna Fish Northwind Traders Smoked Salmon Northwind Traders Granola Northwind Traders Hot Cereal Northwind Traders Vegetable Soup Northwind Traders Chicken Soup $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Quantity 25 50 0 15 0 0 0 0 40 0 0 0 23 0 0 325 0 0 60 120 80 40 80 0 0 60 20 125 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1063 HLSC 147 Week 1 Lab Week 1 Lab: Excel Basics Components of the opening worksheet screen Make sure everyone knows how to open Excel on their laptops, then go over the different components of a new, blank Excel workbook. Any students using Macbooks should have the Excel2016Mac.pdf available. Backstage View To display, click on the File tab. Here you can save, print, export, etc. the workbook. Modify Workbooks 1. Open a new, blank Excel workbook. 2. Rename the Sheet1 worksheet to 2015 and change its tab color to green. a. Double-click on the bottom tab and replace “Sheet1” with “2015” b. Hit enter c. Right-click the tab and click on Tab Color d. Select the desired color 3. Create a new worksheet named 2016 a. Click on the + sign at the bottom tab b. Rename by double-clicking on the tab and replacing “Sheet1” with “2016” then hit enter 4. Make sure the worksheets are tabbed in order. a. To change order, click on the sheet tab and drag to new position 5. Save the new workbook as Week1Lab.xlsx 6. Open the Excel workbook: MLATSubW2.xlsx 7. Copy the Subsample worksheet to the Week1Lab workbook, then change the worksheet name to Participants. a. Right-Click on the tab labeled “Subsample” b. Select “Move or Copy” c. Place a checkmark in the checkbox by “Create a copy” at the bottom of the Move or Copy window d. Under “To book:” click on the down arrow e. Select “Week1Lab” f. Select (move to end) and click on OK g. Double-click on the “Subsample” label and change to “Participants” then hit return 8. Save the Week1Lab workbook. 9. Copy the Measures worksheet from the MLATSubW2 workbook to the Week1Lab workbook. a. Right-Click on the tab labeled “Measures” b. Select “Move or Copy” 1 HLSC 147 Week 1 Lab c. Place a checkmark in the checkbox by “Create a copy” at the bottom of the Move or Copy window d. Under “To book:” click on the down arrow e. Select “Week1Lab” f. Select (move to end) and click on OK 10. Save the Week1Lab workbook 11. Close the MLATSubW2 workbook. 12. In the Week1Lab workbook, hide the 2016 worksheet. (right-click the sheet tab, then click Hide) 13. Make it visible again (right-click any visible sheet tab, then click Unhide. Select the worksheet you want to redisplay). Modify Worksheets A worksheet is a set of rows and columns. In Excel, rows are identified by number and columns are identified by a letter. When using an Excel worksheet to store a dataset, each row is considered to be a record and each column is considered to be a separate variable. 1. On the Participants worksheet, insert a new row 2. (Select the row, right-click, then select Insert). 2. After you insert the new row and while it is still selected, clear the formatting. a. Click on the Insert Options action button (the paintbrush that appears on the lower left corner of the row) and then click Clear Formatting OR b. Under Home/Editing, click the arrow next to the Clear Button and select Clear formats 3. Delete cells A2:H2, shifting the remaining cells up. a. Select the cells, then right-click b. Select Delete c. Make sure “Shift cells up” is selected then click on OK 4. Select cells E1:E20 and move them to I1:I20 a. Point to the right edge of the selected cell range b. When the pointer changes to a four-headed arrow, drag the cell range to its new position. c. If necessary, click OK to confirm that you want to delete data in the target cells. 5. Undo the move. (Undo Arrow) 6. Save changes. Apply Conditional Formatting If you do not have a lot of data in your workbook, it’s sometimes easier to visualize patterns if you apply some conditional formatting tools. You do this by selecting the range you want to work with, then highlight cells meeting the criteria you specify. 2 HLSC 147 Week 1 Lab 1. For example, each record should contain a unique identifier. Before you analyze data, you often want to ensure there are not duplicate records. To see if there are any duplicate records: a. Open the Measures worksheet b. Each record is identified by the variable “StudyID” (the first column) c. Select the data in cells A2 through A27 d. Choose Home -> Conditional Formatting e. Choose Highlight Cells Rules -> Duplicate Values f. The Duplicate Values dialog box appears. The left drop-down list has Duplicates selected by default. Since there are so many, let’s click on the drop-down and select Unique. g. It is fairly obvious with this example that there are a lot of duplicates, but it is not always so obvious. 2. For example, click on the Products worksheet in the MLATSubW2 workbook and take a look at the table provided. The identifiers are provided in the first column (Code). Can you easily identify the duplicates? a. Select the StudyID data by right-clicking anywhere in the first column, then click Select -> Table Column Data. b. Excel automatically selects the column data. c. Choose Home -> Conditional Formatting d. Choose Highlight Cells Rules -> Duplicate Values then click OK e. Scroll through the column and all the duplicates are highlighted in red. 3. Close the MLATSubW2 workbook. 4. Save the Week1Lab workbook. 5. Now take a look at the Measures worksheet again. Notice that the first column is Column B. Where is Column A? 6. Column A has been hidden. Normally you can unhide a column by selecting the adjacent columns and clicking Unhide. But the first column is a little trickier. To unhide Column A: a. Move the cursor to the border between the arrow in the upper left-hand of the worksheet and Column B. When it turns into a double-arrow, double-click or drag to the right to make Column A visible. b. As you can see, in this data file, there is actually a unique identifier based on StudyID and VisitN. This is because each participant in the MLAT study file had up to 4 clinic visits, where their vital signs were measured at each. c. Before analyzing study participant data, always check to make sure that there are not multiple records per person. 7. Suppose you wanted to see if anyone in is obese (BMI > 30). You can use conditional formatting to mark records that meet these criteria. a. Select the data in the BMI column. b. Choose Home -> Conditional Formatting 3 HLSC 147 Week 1 Lab c. Choose Highlight Cells Rules -> Greater Than d. Type 30 in the box on the left, then click OK e. All of the participants with BMI > 30 are highlighted in red. 8. To remove Conditional Formatting rules: a. Select a cell in the range that includes the conditional formatting rules you want to remove. b. Choose Home -> Conditional Formatting -> Clear Rules c. If you want to remove a specific rule, choose Manage Rules instead d. Select the Rule you want to remove e. Choose Delete Rule 9. Save the Week1Lab workbook. Summarizing Data with Subtotals Excel can automatically calculate subtotals. Suppose you wanted to calculate the average age of the participants. 1. Go to the Participants Worksheet 2. Highlight the data within the Age column. 3. Look at the bottom of the worksheet and Excel automatically calculates the average age, the number of records (count) and the sum of the ages. You can also calculate subtotals for different groups of variables. But first you should sort by the grouping variables. Suppose you want to see each participant’s average blood pressure and BMI over all their visits. The data are already grouped by StudyID in the Measures worksheet. 1. Go to the Measures worksheet 2. Select any cell in the data, then Choose Data -> Subtotal a. In the first drop down for the “At each change in:” box, click the down arrow, then select StudyID. b. Under “Use function,” click the down arrow, then select Average c. Under “Add subtotal to:” place a check in the box next to SysBP, DiasBP and BMI, then click OK. d. Excel automatically outlines the data by StudyID and calculates the averages for each participant, regardless of how many clinic visits they had. 3. Save the Week1Lab workbook. 4. Close Excel Open the Welcome to Excel Tutorial and start to work through it You can either open Excel again and select the Welcome to Excel tutorial from the right, or you can download and open the Welcome to Excel tutorial provided in Canvas. 4 ... Purchase answer to see full attachment