Instructions are given in the following files.
Session 2020/2021, Semester 1 Created by Chong Chee Jiun Page 1 of 3 PRX1042 - Laboratory 2 Assignment Submission Guidelines Learning Objectives: ➢ Visualize the pattern in data using conditional formatting ➢ Choose and create appropriate chart to represent tabular data This is an individual assignment. The software that you will mainly use to complete this assignment is Google Sheet. At the end of this assignment, you will submit the Google Drive URL which will lead to a spreadsheet workbook file containing FIVE tabs of worksheet. In other words, the type of item that you need to submit is the same as the assignment submission for LU4. In this assignment, each of you needs to make use of data set provided in eLeap. The data set that you will be using for this assignment will be based on your ICT subgroup name. For example, if you are in ICT subgroup H2C, then you will use data set by the name C; if you are in ICT subgroup H4E, then you will use data set by the name E. What to do next after you have obtained your data set? I would recommend you to immediately upload the data directly to Google Drive so that you can modify it using Google Sheet. In Google Sheet, you will further manipulate the data, meaning you might need to do conditional formatting, do data sorting, copy part of data that you need, paste into new worksheet tab, create chart, and etc. The purpose of manipulating the data is to prepare good evidence such that it could support your answer for each of the questions listed in Table 1. In the spreadsheet that you are going to submit, each worksheet tab will contain your answer and supporting data for a question listed in Table 1. Do arrange your answer and supporting data using arrangement shown in Figure 1. Figure 1 Example statement for explaining how you created tabular data and chart: “I first copied and pasted the header of original data set in row 7. Then, I used custom sort to sort the data set according to year and next according to Race. After that, I selected one row for Asian to be copied and paste in row 8, because Asian was found to just occupies Woodland City in year 2000. For White citizen, I copied three rows consisting different location of home and pasted at row 9 until 11. Next, I use spreadsheet formula to calculate the difference required by the question as shown in cell E12.” Session 2020/2021, Semester 1 Created by Chong Chee Jiun Page 2 of 3 Table 1 Tab Question with hint 0 No question here. Just copy your original data set in here. This is the space where you may sort your data, do conditional formatting, etc. This is your main data source, DO NOT simply delete any data in here. 1 Calculate the difference between the total number of populations for Asian citizen with the total number of populations for White citizen in year 2000. Hint: You will need to provide a table showing the original data rows that you copy from your data set. The data rows should only be related to the total number of populations for Asian and total number of populations for White citizen. Do NOT copy data rows that do not contain the information you need. You might need to do sorting in the original data set before starting to copy the data rows. You may also create cells that do calculation showing the difference. (5%) 2 What is the total number of populations that uses Bicycle to work for both year 2000 and year 2006-2010? Hint: Similar hint as Tab 1, but this time you might need to use SUM formula or perhaps you want to consider using Pivot Table with Slicer. (5%) 3 What is the number of Asian that uses “Car, Truck, or Van: Carpooled” in year 2006-2010? Hint: I guess you know what to do by now without a hint…? (10%) 4 Which location of home has the highest population that chose to “Worked at Home”? Hint: Besides following hints from tab 1, another hint would be to provide an appropriate chart that could easily shows the highest population. Perhaps you want to consider using Pivot Table with Slicer. (10%) 5 Is the distribution for total population of each ethnic similar for both years? Hint: You might want to use Pivot Table with Slicer. If you would like to verify the increment rate, use the formula: {difference in both year ÷ Total population in year 2000} × 100% (10%) Session 2020/2021, Semester 1 Created by Chong Chee Jiun Page 3 of 3 Full marks will be given if the following criteria are achieved: 1. All instructions are followed closely. 5% will be deducted for any noncompliance. (10%) 2. Content within all spreadsheets are well arranged and easy to navigate around. (10%) 3. Answers for all questions are well supported by appropriate data table or graph. (Tab1+Tab2+ Tab3+Tab4+ Tab5 = 10+10+20+20+20 = 80%) Note: Tab0 will not carry any mark. However, if it is not there, it means you did not follow the guideline instruction closely enough. Some marks will be deducted from criteria 1.