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) Initiate “RULE #1” when you
receive a dataset
- 2) Add thirty (30) columns
between the PLUG and the “series_id” in the original dataset and title
them as follows:
![Table
Description automatically generated](file:///C:/Users/pc/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png)
- 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) 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) 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))
![page2image6943744](file:///C:/Users/pc/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png)
- 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) 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) Sort ALL of the data
by the following fields:
![page2image6944368](file:///C:/Users/pc/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png)
- 9) In columns 10 to 14, write
code that correctly identifies each county in the correct region.
- 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) 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) For columns 05 to 07, use
the field name to show the proper analytics keys.
- 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”).