COVID-19 datasets

Tutorial Assignment TA1: Data Analysis in Excel
Print
Overview:
In Tutorial Assignment #1, we will be using COVID-19 datasets from 2 states for 1 particular month.  After carrying out a set of tasks (listed below) to better prepare the data for analysis, you will then make a rudimentary comparison between two tables (i.e. sets).

For this semester, we will be generating daily totals (non-cumultive) of Daily Confirmed cases and Daily deaths for Arkansas and Louisiana in October 2020.

Preliminary Instructions/Preliminary Task:
Important: These directions are for the PC version of Excel, though they should work for Excel 365.

If you need free student access to Office 365, a free student copy of the Office software OR would like instead to carry this out using a “virtual visit” the university’s computer labs please see Obtaining the software you need for this course.

To get credit for this assignment, you must complete the preliminary task plus all 5 of the required tasks (see below).

For each of these tasks, the task is described. with the detailed instructions on the right of the task description.  This is because some of you may not even need the detailed instructions.

You also have been provided with tutorial resources to help guide you on many of the Excel skills needed in this assignment.  You can access these here.

Preliminary Task:    More detailed instructions:
Download the TA#1 Spreadsheet TA1_Fall_2021_Arkansas_Louisiana.xlsx ) and then rename it  to “TA#1_firstname_lastname_MIS310_section#” (for your convenience it is also attached in the assignment folder for TA1, ready to download)
In the first worksheet in this file spreadsheet, input (inot the top row) your first name, last name and student ID#.
Copy the tab “Dataset-COVID.” and rename the copy TA1 – then save your work.
Why copy? If you mess up or need to redo, you will always have the original dataset to go back to start over with.
Directions for preliminary Task – how to copy a worksheet tab

Hover the mouse pointer over the tab “Dataset-COVID” and right click on the mouse. You will see a Pop-up box listing Insert, Delete Rename, Move or Copy, etc.

Click on ‘Move or Copy…’. In the next pop-up box that appears, check the box for “Create a copy” and then click on “(move to end)”

Click ‘OK’ and a new copy tab should be created with the same name but with (2) behind it.
Ex. Dataset-COVID (2)

Rename the new tab by double clicking the tab or right clicking the mouse on it and choose ‘Rename’. The rest of your work for this assignment will be done in this tab.

Save your work so far.
Assignment Instructions: Your 5 tasks
Some context for the next 5 Tasks of this assignment:

You are looking at the daily cumulative totals for COVID in 2 particular states for one particular month.  Your overall job is going to perform some basic analyses of the data as the basis for then making a comparison between the incidence of COVID between the 2 states.

You will be carrying specific guided tasks in order to create these analyses using spreadsheet formulas and functions,  To get credit for this assignment, the following 5 tasks need to be completed. You’ll be using you new tab ‘TA1 ’i.e. (the copy of the source data in order to complete these tasks. Please follow each step carefully. Skipping steps may result in inaccurate outcome.

Tasks:

Task 1: Create 2 tables: one for Arkansas data and one for Louisiana data and name the table “Arkansas” for Arkansas and “Louisiana” for Louisiana

Task 2: Create a new column named Daily Confirmed cases and Daily Deaths. Because the data for Confirmed and Death are cumulative total from previous months, these will have to broken down to daily totals using a formula that you will enter. (You have been provided with the data for the last day of the previous month.)

Task 3: Calculate the following for each of the daily (not cumulative) totals columns, (should start for the first of the month till the last date of the month), for each state and for daily confirmed cases and daily deaths:

Minimum (MIN)
Maximum (MAX)
Average (AVERAGE)
Total (SUM)
Task 4: For each State’s table, create a new column after Country/Region and label the new column ‘Ratio’ (done for both tables)

In the new column ‘Ratio’, write a formula to calculate the ratio of ‘Death’/’Confirmed’
Format the cells in Ratio as percentage and with 2 decimals
Task 5: In a one page WORD document, please try to offer an in-depth analysis as to what can you analyze from the data in comparing the 2 states for this month?

For a view of what each table will look like in the end after Task 4, please see this PDF of a modifed COVID data table for the state of Ohio in September.

Task #    More detailed instructions:
Task 1: Create a table for Arkansas and Louisiana data each and name the table “Arkansas” for Arkansas and “Louisiana ” for Louisiana
Click on cell A3, and highlight all of the columns and the rows of the data for the first state’s data, including the data labels in row 3.

From the HOME tab, click on ‘Format as Table’ and choose a (nice colorful) template in blue.

A box should appear, make sure to check “My table has headers”

Click OK and a table should be created for that data table.

Click on the table and now you see a new tab along the top menu called “Table Design”.

Use the Table Design, Table Name option to change name Table Name.

Do the same for other state’s data (which is a few columns over to the right).
Task 2: Create a new column named Daily Confirmed cases and Daily Deaths. Because the data for Confirmed and Death are cumulative totals from previous months, these will have to broken down to daily totals using a formula that you will enter.
(You have been provided with the data for the last day of the previous month so you can start by calculating the daily data for the first of the month)
In the first state’s table, right click on the cell at the top of the column for the Cumulative number of Deaths in order to highlight all cells in that column and use the dropdown option and click “Insert” to insert a column just to the left.

Name the new column “Daily Confirmed cases”.

Right click on the cell at the top of the column for the Country/Region in order to highlight all cells in that column and use the dropdown option and click “Insert” to insert a column just to the left.

Name the new column “Daily Deaths”

For the first state for the first day of the month (not the last day of the previous month) enter the following formula into the upper cell for the column for “Daily Confirmed cases” = Current day’s cumulative Confirmed cases – the Confirmed cases for the day before (i.e. the last day of the month).  This will yield the nmber of daily confiormed cases for the first of the month.

Copy this formula for the first day’s daily total and post this into the remaining days of the month in this column.

Repeat these steps to create data for the daily total of deaths.

Repeat all these prior steps for the other state’s table (i.e. creating data for each day’s confirmed cases and deaths for the other state).
Task 3: Calculate the following for each of the daily (not cumulative) totals columns , for each state and for daily confirmed cases and daily deaths:
• Minimum (MIN)
• Maximum (MAX)
• Average (AVERAGE)
• Total (SUM)

(Obviously this should start for the first of the month till the last date of the month – NOT including the last date of the prior month)

In the table for the first state, for the first column of data (i.e. Daily confirmed) move the cursor to the cell 2 rows below the end of the data (i.e. 2 cells below the Daily Confirmed cases for the first state on the last date of the month.

Compute the Minimum Daily confirmed cases for the month by typing in the Minimum function followed by an open parentheses, then use the up arrow to move up the first day of the month’s Daily Confirmed cases and drag the mouse down till the last days of the month’s daily confirmed cases, then type a close parentheses to complete the function. You have created a function listing the minimum value of the daily confirmed cases in the month.

Just below the Minimum function, repeat this with the Maximum function to display the maximum value of daily confirmed cases in the month.

Just below the Maximum function, repeat this with the Average function to display the average value of daily confirmed cases in the month.

Just below the Average function, repeat this with the Sum function to display the total of daily confirmed cases in this month.

Repeat steps 1 thru 5 in the column listing the daily deaths.

Using the same steps 1 thru 6 do the same for the other state’s data (i.e. the Minimum, Maximum Average and Total (i.e. Sum) for both Daily Confirmed cases and daily deaths.
Task 4: For each State’s table, create a new column after Country/Region and label the new column ‘Ratio’
In the first state’s table, right click on the cell at the top of the column for the Country/Region in order to highlight all cells in that column and use the dropdown option and click “Insert” to insert a column just to the left. Name the new column “Ratio”

In the new column ‘Ratio’, in the cell for the row for the first day of the month write a formula to calculate the ratio of Daily Deaths/Daily Confirmed cases. Then copy this formula into all the cells in this column (i.e. calculate a ratio of deaths to Confirmed cases for every day of the month).

Format the cells in the Ratio column as percentage with 2 decimals

Repeat Steps 1 thru 3 for the other State.
Task 5: In an accompanying one page WORD document, please try to offer an in-depth analysis as to what you can analyze from the data in comparing the 2 states for this month?
How to Submit the Assignment:
You will upload your Excel Spreadsheet file and your WORD document file to the TA1 folder.

How the Assignment is graded:
Grade    Criteria met
100
All tasks were completed accurately as required
The write-up for your solution is written clearly
The write-up for your solution sets forth its reasoning and logic – the student’s data analysis justifies the conclusions that the student drew in their analysis and recommendations
90
All tasks were completed accurately as required AND

The write-up for your solution is written clearly OR
The write-up for your solution sets forth its reasoning and logic – the student’s data analysis justifies the conclusions that the student drew in their analysis and recommendations
80
All of the criteria listed above were met though one task was not performed correctly AND

The write-up for your solution is written clearly OR
The write-up for your solution sets forth its reasoning and logic – the student’s data analysis justifies the conclusions that the student drew in their analysis and recommendations
70
More than one task was not performed correctly

AND

The write-up for your solution is written clearly OR
The write-up for your solution sets forth its reasoning and logic – the student’s data analysis justifies the conclusions that the student drew in their analysis and recommendations