Description

EXCEL PRACTICE

The dataset provided shows monthly unemployment information for the entire state of California and all fifty-eight (58) counties across four (4) attributes (for a total of 2,128 rows of data) from 1990 to 2018.

Data Setup and Table Relationships

1. 1)  Initiate “RULE #1” when you receive a dataset
2. 2)  Add thirty (30) columns between the PLUG and the “series_id” in the original dataset and title them as follows:

3. 3)  Insert the following files as reference tabs into the file titled:
   “00 - la.data.11.California (Annual -- State and County Only)--Data Only.xlsx”.
• 01 – Seasonality.xlsx
• 02 - Series.xlsx
• 03 - State and Counties.xlsx
• 04 - Measure Code.xlsx
• 05 – Footnote Codes
4. 4)  In the “State and County” tab, identify the “Region Desc” (Vlookup) and the “Region and Desc” (Concatenate “Region” and “Region Desc”)—currently shown in grey.

5. 5)  Parse the Series ID using the following guide into the corresponding fields added to the model: Series ID Example: LAUCN060090000000005 (Employment: Calaveras County, CA (U))

6. 6)  To create the data in “Series Complete” (Column 24), either use the trim command along with all but the last two characters of the series_id or CONCATENATE columns 17-23.
7. 7)  In columns 15 and 16, use the county (column 22) to identify the region and then return a Concatenated version from the “State and County” tab.
8. 8)  Sort ALL of the data by the following fields:

9. 9)  In columns 10 to 14, write code that correctly identifies each county in the correct region.
10. 10)  In column 09 (“County ID”), take the sum total of the columns 10-14 but force the calculated value to be a two-digit result (Hint: you are going to need to use the “RIGHT” function for this one).
11. 11)  In column 08 (County ID Repeated”), write code that will show the value above if the “County ID” is equal to “00”; otherwise, the same value as the “County ID”.
12. 12)  For columns 05 to 07, use the field name to show the proper analytics keys.
13. 13)  For columns 02 and 04, use the County ID to identify the “State ID” found in the “State and County” tab. The data for column 03 us the same as column 26 (“County Desc”).