Open a document file $$ this Is where you will post your answers to the $5$ questions set forth below. Save it as TA$\_$your last name.

Download the TA$2\_$Fall$\_2021\_$Arkansas.xlsx $($TA$2\_$Fall$\_2021\_$Arkansas$)$ file and save it as TA$2\_$your last name. For your convenience, this file has been attached to the assignment folder for TA$2.$ This is a list for all Arkansas counties of the cumulative cases and deaths from COVID over a period of about a year and a half. You have also been provided with a Read$-$only example spreadsheet file for the required operations for this assignment as carried out on the same data for Ohio, albeit for a different month.

Go to the Province$/$State column and make sure that only the rows for Arkansas are selected and the blanks rows are not.

Add a new worksheet, relabeling it Sorted

Copy the Arkansas source table from the worksheet labeled Raw data into the worksheet Sorted and then sort the rows by Date.

Add another worksheet and label it Date filter. Copy the sorted table from Sorted into Date Filter. In the Date filter worksheet perform a filter on the table to list only rows of COVID data between $($& including$)9/30/20$ and $10/31/20($i$.$e$.$ click on the label for Date at the top of the column and choose Date Filters $-$ Between, and then enter the dates as upper and lower bounds.$)$

Add another worksheet and label it Source. You will copy the table from Date filter into it BUT you will do this a little differently. As you copy the filtered data from Date Filter into Source you will not just paste it in; Instead, you will do a $$Paste Special$$ to copy in only the values of the data.

Note that once you have done this the values for the Date in Source are no formatted properly. Select that column and then format these using the Format Cells command to set these up as Dates again in the mm$/$dd$/$yy format.

Now it is time to create a pivot table that will summarize the data for this state by date across counties.

Highlight all the data and then use the insert Pivot table to create a pivot table form in a new worksheet. Rename the new worksheet Pivot.

In Pivot, if you do not see a field list, right click on the Pivot table area and click on Show Field list. In the Field list you will:

Right Click on Date and Choose Add to Row labels

Right Click on Confirmed and Choose Add to Values

Right Click on Deaths and Choose Add to Values

Finally, right click on the Date column in the pivot table and choose $$Ungroup$-$ this will reset the date value back to the mm$/$dd$/$yy format. In the date column to the Pivot table, please uncheck the box for "Blank" $-$ this removes any count of null values.

You are now looking at a pivot table that is very close to the data for Arkansas you were given for the first tutorial assignment.

Scroll down to the Sum of deaths for $10/1/20$ and double$-$click on it$.$ A new worksheet will appear $$ rename this worksheet Start of Month

Q$1$: Describe what data you are seeing in the Start of Month worksheet.

Copy the Pivot table from the Pivot worksheet and use paste special $($values$)$ to copy it into a new worksheet $$ that is to be labeled New source. Once again you will have to reformat the date column to the mm$/$dd$/$yy format $-$ AND you should change the title of this column to "Date".

In the New Source worksheet Scroll down to the Sum of deaths for $10/1/20$ and double$-$click on it$.$

Q$2$: What happens now? Is this different from before? Why?

Copy the data from New Source into yet another new worksheet $$ rename the new one New Calc.

In the New Calc worksheet please redo the same calculations you did in the first tutorial assignment for October $1$ thru October $31$: Daily cases, Daily deaths and Ratio.

Highlight the Ratio column from Oct $1$ to Oct $31$ and use Home $-$ Conditional Formatting to overlay the Ratios $($i$.$e$.\%$ death rates$)$ with a color scale with Red being the highest values and Green the lowest.

Next use Conditional formatting on the daily cases column for October to insert a rule that daily cases that above the average should be in red font. $($i$.$e$.$ Home $-$ Conditional Formatting $-$ New Rule $-$ Rule Type: Only Values that are above or below average $-[$Format values that are above the average $-$ set font color to Red.$])$

Q$3$: Do any of the days highlighted with the highest ratios $($death rates$)$ also have the highest cases? How many?

Q$4$: Do any of the days highlighted with the lowest ratios $($death rates$)$ also have the highest cases? How many?

Q$5$: Quantitatively speaking, what do you think this $($i$.$e$.$ your answers to Q$3$ & Q$4)$ tells us about using a death rate $($ratio$)$ as an indicator of the spread of COVID?

Reorder the worksheets into the following order:

Raw Data

Sorted

Date filter

Pivot

Start of Month

New Source

New Calc

Source