Analyzing COVID19 Dataset You are given a dataset in relation to COVID19 record up to July 2020 (covid19_July2020_dataset.xlsx). The dataset contains data such as country name, region, population, total confirmed cases, total death, total recovery, and active case. Your team has been assigned to perform data analysis and construct a couple visualisation using spreadsheet software such as Microsoft Excel.

TASKS

1) Creating New Spreadsheet Within the dataset workbook, create a separate spreadsheet named “Solution”, construct a simple table and include all team members’ name, email address and percentage of contribution. Provide an appropriate table header and highlight the team leader’s name. NOTE: You are expected to format and style all the tables in this spreadsheet for subsequent questions appropriately.

2) Top 10 Countries with COVID19 Create another table below the team members table, list down the top 10 countries with highest total COVID19 cases and indicate the figure (total cases).

3) Statistics Use Excel formula, calculate the lowest, highest, total and average of the population, total COVID cases, total death, total recovery and total active cases for all countries from the dataset given. Include the statistics at the end of the table within the Raw Data spreadsheet.

4) Probability Insert a column after “Total Deaths Due to Covid” column in the raw data table and label it as “Probability of Death”. For each country, calculate the probability of death using excel formular. Afterthat, insert another column after “Total Recovery” column in the table and label it as “Probability of Recovery”. For each country, calculate the probability of recovery using excel formula. Format the probability values to display in percentage with 2 decimal places. Lastly, create a new table at the solution spreadsheet and indicate the country name with its probability to answer the following questions: a) Which country will have the highest probability of death if a person is diagnosed and tests positive with COVID?

 b) Which country will have the lowest probability of recovery if a person is diagnosed and tested positive for COVID?

5) Data Aggregation & Consolidation a) Use the “CountIF” function to calculate a total number of countries within each region. b) Extract and consolidate the total cases, total death, total recovery and active cases for the 10 ASEAN countries (Google and identify which are the 10 ASEAN countries). Exclude any countries with missing data OR countries with total cases below 50,000.

6) Data Visualisation In the solution spreadsheet, create the following charts: a) Create a bar chart to show the number of total cases for the top 10 countries with highest COVID cases. b) Create a pie chart to show the distribution of countries for each region. c) Create a stacked bar chart to show the extracted date for the 10 ASEAN countries (after exclusion)