Assignment #2 – Creating Charts Using Online Data

Due: Wednesday, October 14, 2020 (4:00 PM)

Complete all your work in one Excel workbook. It is preferred that students work in pairs on the assignment, with one assignment submitted per pair; both students in a pair will receive the same grade. It is strongly recommended that both students actively participate in preparing the assignment. If, instead, partners choose to split the assignment (with each being responsible for different parts of the assignment), be aware than one person’s poor/non-completion of his/her portion is NOT an acceptable reason for submitting the assignment late. You may work with a student in the Monday section; if you do, please note that when you upload your assignment.

At the completion of your assignment, you should have one workbook with six tabs). Name the file as follows: OurNamesAssign2.xlsx (or NamesAssign2.xls), where you replace OurNames with your first initials and last names. Your name(s) must be a part of your file name. Upload your completed Excel document to Blackboard on the assignment page.

INSTRUCTIONS – READ CAREFULLY:

Answer each question on a separate worksheet within one workbook; label each worksheet with the question number. You will need to enter the necessary data into the worksheet and create the requested chart on the same worksheet. Charts should have an appropriately descriptive title with data labeled if necessary; to make the charts professional-appearing, you may want you to: change the size of the charts, adjust font size, change colors, format numbers and decimal places appropriately, add axis titles when needed, etc. If the question only requires a table, it should have an appropriately descriptive title; additionally, you may want to add color and borders, align data and headers in columns to make the table easy to read, etc. To receive full points on a question, your tables and charts should be formatted beyond the default.

Links to necessary web sites for this assignment can be found in the “External Links” tab on Blackboard.

Question 1

Using “Ask CHIS” on the California Health Interview Survey website, find the percent of persons covered by Medi-Cal in San Diego County and Los Angeles County for the years 2012, 2015, and 2018. Include both sexes together, ages 18-64. You will need to compare the counties to each other.

1. Enter the data into an Excel worksheet.
2. Create a column chart with the data grouped by year. Add data labels.

Question 2

Using the Johns Hopkins Coronavirus Resource Center data, create a table with the number of confirmed Covid-19 cases, number of deaths, case-fatality rate, and deaths per 100,000 population for the following countries: Canada, China, France, Germany, Japan, South Korea, Spain, and the US. Include the date you accessed the information in the table title. (HINT: Click on “Tracking” and then on “Critical Trends” to find the mortality analyses.)

A. Create one table in Excel containing all of the requested data.

Question 3

Using the World Health Data Repository, find the infant mortality rate (probability of dying between birth and age 1 per 1,000 live births; both sexes combined) for Germany, Japan, Mexico, the Russian Federation, and the US for the years 2014, 2016, and 2019.

1. Create a table in Excel with all of the requested data.
2. Create a column chart with the data grouped by year. Show data labels.

SEE PAGE 2 FOR QUESTIONS 4 – 6.

Question 4

Using this information from the World Health Data Repository, create a line chart (with markers) showing lines for each country from the oldest date to the most recent date. Place the legend on the right side of the chart.

*Source: WHO Data Repository; accessed 9/20/2020

Place the legend on the right side of the chart.

Question 5

Using the hysterectomy data and frequency distribution table you built for Assignment 1, create a histogram with the percent of women having hysterectomies in the age groups requested in Assignment 1. (You should be able to copy the necessary columns into a worksheet in the workbook for this assignment by using “Paste Special” and selecting “Values and Number Formats.”) The histogram can be created based on a bar chart. Add data labels to the histogram.

Question 6

Using the National Center for HIV/AIDS, Viral Hepatitis, STD, and TB Prevention Atlas (available through the CDC Database Systems link), create a column chart with the percentage of the total population who are uninsured for 2012, 2014, 2016, and 2018 in Louisiana, Oklahoma, Texas, and Washington. Group by state. (HINT: This database also includes information other on disease; you will need to find it.) Show data labels with no decimals.