The purpose of this assignment is to provide students with a framework to understand how to use conditional functions to categorize data in Excel (or any other program thereof).
PART I: Data Setup
- Import the information on the US states and bring this into the Bird Strikes Model (in another tab).
- Import the foreign country information into the same tab as the states (but off to the right side of the states’ information).
- Create a table to the right of the foreign country information which shows the regions of the US but with an identifier number.
- Create two columns: one for the “Ordinal Code” and the other for the “PLUG” values.
- Add additional columns for supplemental data you will need.
- Create a VLOOKUP which results in the abbreviation for each of the states in the US.
- Change all “DC” references to show “MD” in another column.
- Setup an over-ride for all foreign entries to read “Other”
- Combine all of the state information to show “Other” if the foreign over-ride is present. If the “MD” is shown for DC, then show the “MD”; otherwise, show the original state abbreviations.
- Create a VLOOKUP which shows the region with the codes for each region.
- Create columns showing the following data:
- Month
- Year
- “Month”/1/”Year”
- Create categorical text analysis which shows the following key words:
- Smithsonian
- Bird
- Engine
- Feather
PART II: Answer the following questions
- How many bird incidents were reported in 2009, 2010, and 2011?
- How many incidents were noted as “Airplane” and “Helicopter” in 2009. How many are unknown in 2010?
- How many incidents were reported in August 2010?
- How many times was the word “bird” noted in remarks for 2009, 2010, and 2011?
- How many incidents were there in the “Middle West” in 2011? How many of those occurred in April 2011?
- In 2010, how many incidents occurred in DC with an “Airplane” but that also noted the word “Smithsonian”? How many occurred with a “Helicopter” with the word “Smithsonian”?
Click on “ORDER NOW” to get it answered just for you!