Building Excel Reports Many professions require the collection and presentation of data. For the UK profession, the data are typically from usability studies. Part of being a professional in any eld is the ability to analyze and present data in a form that is useful to your organization, colleagues and other stakeholders. Exercise 1 will acquaint you with some basic concepts behind reporting data from a usability study, with an emphasis on Excel. Exercise 1-Excel File The data for the usability study to be reported for Exercise 1 are found in "Exercisel-Excel File". Some notes about this Excel file: 1. The le has three tabs: one labeled Presentation, one labeled Analysis, and one labeled Data. Separate tabs can be useful for reports and dashboards. rather than using a single large sheet. 2. The Data tab contains simulated data from 8 different tasks in a usability study with 15 participants. . The only metric collected for Tasks 14 is whether or not the participant completed the task i1=Completed, D:Did not complete}. - There are two different metrics for Tasks 5-8: - one metric was Level of Success, using a scale described in Section 4.1.2 of \"Measuring the User Experience: 1 = no problem. 2 = minor problem, 3 = major problem, and 4 = failurefgave up. - The second metric was Time-on-Taslt [TOT] in seconds. The task was stopped if the participant exceeded 10 minutes [500 seconds:- - therefore the maximum value is 600. 3. You will not be changing the data in the Data tab. You should review the data in the Data tab, then move on to Performing the Exercise, which involves creating summary tables of the data {which will go in the Analysis tab] and charts ofthe summary tables [which will go in the Presentation tab}. Performing the Exercise 1. Save a copy of the \"Exercise 1-Excel le\" le with your last name. Use lite le name \"Exercise 1-syuur last name)\". You will make all changes in your copy of the le and submit this le to the Exercise 1 Assignment when you are done. 2. Create Summary Tables in the Analysis Tab In most cases. raw data collected in a usability study are not displayed in tables or charts. Instead, data typically need to be summarized into an acceptable intermediate form, such as a summary table showing frequency counts or means. For this Exercise, you will create summary tables for the raw data found in the Data tab. These summarv tables will he created in the \"Analysis" tab. The \"Analysis" tah worksheet should look like this when you have completed all of the steps below: A A | B | C I D l E l 1_Tasks 1-4: Task Completion 2__ Task 1 Task 2 Task 3 Task 4 3__ Completed 13 5 11 15 4_ Did not complete 2 10 4 D 5__ s_ T__Tasks 5-3: Level of Success 3 Task 5 Task 5 Task 3' Task 8 9__Level of Sucre 55:1 5 10 5 9 Iglevel of Success=2 5 4 S 5 1LLevelofSuocess=3 1 1 4 D 11_ Level of Euoce 55:4 3 D 1 1 I3_ 14. IiTasks 5-3: Time on Task 16 Task 5 Task 6 Task 7 Task 5 IT-MeanTlmeonTaslt 292.733 154.067 275.467 1.51.2 Ia_5tandardDeviatlon 213.704 135.663 19145? 162.309 Figure 1 - Completed \"Analysis" tab for Exercise 1. A. Steps to create the table forka Cornpletion data for Tasks 1-4: 1. Select the "Analysis\" tab. 2. In cell A1. enter "Tasks 1-4: Taslt Completion.\" 3. Enter the column headings in cells Bl, C2, oz, and E2 ("Task 1", \"Task 2", "Task 3\". \"Task 4\"} and the row headings in cells A3 and M (\"Completed\". "Did not complete" 1 as shown above. 4. In cell as. enter the following function: =cour~lTll=[oatalc3:c1?, '1":- and then press Enter. "' Note: When you press Enter. you won't see the function in cell 33. Instead, the EDUNTIF function looks for the instances of "1" in the range of cells from c3 to {21? on the Data worksheet and returns the total count [number of cells with \"1\"]. For this Exercise, the returned value should he 13. When cell 33 is selected, you will see the function in the entry eld above the spreadsheet so you can make changes in that eld if neededf" 5. Move to cell 34 on the Analysis worksheet and enter the following function: =COUNTIFiDatalC3:C17, "0"} and then press Enter. This function returns a count of the number of \"or" values between C3 and {217 on the Data worksheet. For this Exercise, the returned value should be 2. 5. Enter the remaining functions as follows: Cell Function =COUNTIFIIDataIDS:DJT,"1"] =COUNTIFiDataIDS:DJ7, "0"] =COUNTIFI: DatalE3:E17, "1"] =CDUNTIFiDataIEazE17, "on =cou~Tll= DatalF3:F1?, "1" =COUNTIFiDataIF3:F17, "o":- B. Steps to create the table for Level of Success data for Tasks 5-8: 1. In cell A7. enter "Tasks 5-8: Level of Success.\" 2. Type in the row and column heading labelsfor Tasks 5'8 as shown in Figure 1. 3. Enter the first fourfunctions as follows: Function =COUNTIFiDataiH3: H17, "1"} =CDUNTIF DatalH3: H17 M2" =COUNTIFiDataiH3:H17, "3"} Bl). =COUNTIFiDatalH3:H17, \"4"} 4. Enter the corresponding functions into the cells for columns C, D, and E in the table. This table should now match the one shown in Figure 1. C. Steps to mate the table for statistics [Mean and Standard Deviation} for the Time on Task {TOT} data for Tasks 5-8: 1. In cell A15. enter "Tasks 5-3: Time on Task.\" 2. Enter the row headings {Mean Time on Task. Standard Deviation] and column headings asks. Task 5, Task 7, Task 8} for this table as shown in Figure 1. 3. In cell BIT, enter the function =AVERAEiDatalM3:M17:I 4. Enter the corresponding function {with appropriate changes to indicate the correct column in the Data tab] in cells (317, 017, and E17. 5. In cell I518. enter the function =5TDEV{Data IM3:M17] 5. Enter the corresponding function {with appropriate changes to indicate the correct column in the Data tab] in cells C13, D18. and E18. The summar'tlr tables in the Analysis tab should now match Figure 1. {This is a good time to save your le. before continuing to the next task} 3. Create Chans in the Presentation Tab it. Create a column chart for the summary data for Tasks 1-4. 1. Use your mouse to select the summaryr table in the Analysis tab for the Task Completion data for Tasks 1-4 {i.e.. click in cell A2 and then drag the pointer to cell E4 and then release so that all of the cells for the summary table including the row and column labels - are highlighted]. 2. Click the Insert tat:- on the ribbon at the top ofthe Excel window. 3. Select the first 2-D Column chart option, as shown below. A pop-up with the "Clustered Column" name for this chart option may appear briefly if you hover your pointer over this option. Details of this screen might be somewhat different for different versions of Excel. Formulas Data Review View SmartArt Column Line Pie Bar Area Scatter 2-D Column D ask 3 Clustered Column 3-D 13 Compare values across categories by using vertical rectangles. Use it when the order of categories Task 7 is not important or for displaying Cyli item counts such as a histogram. 9 The chart will be inserted in Analysis worksheet and should look something like the one below. (There may be minor differences because students may have a different version of Excel than the one used to generate this example.) 16 14 12 10 6 ON Task 1 Task 2 Task 3 Task 4 Completed . Did not complete 4. With this chart selected right-click on the chart. A pop-up menu of chart options appears.. Select \"Move Chart..." and another window will appear. [If a right click doesn't result in a menu with the \"Move Chart..." option. you may nd a Move Chart option in the ribbon at the top of the screen.) . Select \"Object in:\". then use the corresponding drop-down to select \"Presentation\