Description

In this exam you will continue analyzing the stock and COVID-19 datasets. This time, we will be combining the two datasets in order analyze what relationship, if any, exists between performance of the index and the spread of COVID-19 across the United States in the first half of 2020.

Open the exam starter file and save it using the following naming convention: LastName_ExcelExam.xlsx

NOTE: Save your work early and often! If Excel starts behaving strangely, then save your file, close it, and reopen it. This almost always resolves the issue. If Excel freezes, then force quit the application and reopen your file. This won't be a big problem if you've been saving along the way.

  Format the data ranges located on the IXIC, COVID_STATE, and COVID_US worksheets as tables.  Give the tables the same names as their worksheets.

  On the COVID_STATE worksheet, format the REPORT_DATE column to Short Date.

  Insert two columns to the right of STATE: REGION. Name the first column REGION_HOLD and the second column REGION. Use Text to Columns to split the data from the STATE: REGION column into two columns using the colon as the delimiter.

  The resulting values in the REGION_HOLD column will contain a leading space. Populate the REGION column by using the TRIM function to eliminate the leading spaces from the values in the REGION_HOLD column.

  Rename the STATE: REGION column to STATE. Create range names of the same name for for the REPORT_DATE and STATE columns. Also create a range name for the PEOPLE_DEATH_COUNT column named YTD_DEATHS. To be counted as correct, the ranges should refer only to the data contained in the table.

  On the COVID_US worksheet, define a range names for the REPORT_DATE and PEOPLE_DEATH_NEW columns. Shorten the range name of REPORT_DATE to DATE. Shorten the range name of PEOPLE_DEATH_NEW to DEATHS. 

  On the IXIC worksheet, insert a column to the right of GAIN/LOSS. Name the column CASES. Use the VLOOKUP function to look up the DATE value in the COVID_US table and return the corresponding value in the PEOPLE_POS_NEW_CASES column. For the formula to be counted correct, the COVID_US table must be specified as the table array and the range lookup must require an exact match.

  Insert a column to the right of the CASES column named DEATHS. Use a combination of INDEX/MATCH to retrieve the appropriate DEATHS value from the COVID_US table. For the formula to be counted correct, the formula must refer to the range names created in a previous step and require an exact match.

In this exercise, we will be building a data table that will allow us to analyze year-to-date COVID-19 deaths for each state in the US. We will build this table manually using array formulas and functions. The basic structure of the table we will be building can be found on the DeathsbyState worksheet. Your final product should look like this (partial screen shot due to table size):

Instruction Files