PH 490A

Spring 2020

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

1

PH 490A Spring 2020

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.

2

PH 490A Spring 2020

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.

Current Health Expenditure per Capita in US Dollars for Selected Countries and Selected Years*

2001

Canada 2054 Germany 2341 Japan 2480 UK 1739 US 4911

2005 2009 2013 2017

3283 4348 5345 4755 3547 4643 5094 5034 2883 3686 4336 4169 3010 3274 4208 3859 6452 4699 8648 10246

*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.

TIPS

TIPS FOR BUILDING PROFESSIONAL TABLES IN EXCEL

1. Make tables self-explanatory: Tables should stand on their own. A reader should be able tounderstand the information in a table without needing additional text.
2. Choose the right font: Choosing a clear, readable font such as Arial or Calibri is a simple first step towards creating an attractive, business-like spreadsheet.
3. Limit use of different fonts: Two different font styles can help to differentiate between headers and main text. More than two, however, can be distracting.
4. Center your title: Use the “Merge Cells” or “Center Across Selection” options to create a professional, centered title for your spreadsheet.
5. Start in b2: Leaving row 1 and column A blank is an easy way to create some space in your document.
6. Bold your headers: Headers that stand out from the main text can help to make your spreadsheet easier to read. Bolding them is a quick and easy way to do this.
7. Vary your font sizes: Using a larger font size for headings and sub-headings makes for a more readable spreadsheet. It’s also a good idea to keep your basic font size large enough to read clearly.
8. Align text: Format your cells and select options that allow you to align your text as best suits your needs. For instance, text aligns to the left by default, while numbers align to the right – so you might need to tidy up tables by giving headings the same alignment as the table contents. Alternately, you may want to center the numbers in your table.
9. Create space: You can manipulate the height and width of cells to keep your spreadsheet from looking too cramped.

10. Leave some cells empty: If your spreadsheet is fairly complex, leaving a row or column empty at appropriate breaks in the data can help to improve readability.

11. Play with grid lines: All those lines separating all those numbers can look very confusing. By opting to show only the lines around your results column, for example, you can make a big difference to the readability of your spreadsheet.

12. Use color sparingly: Colors can be used in Excel to highlight key rows or columns, making the spreadsheet more attractive and easier to read. Don’t overdo it though, as this can produce the opposite effect.

13. Keep to dark text on a light background: If you do choose to introduce color, be sure to stick to dark text on a light background, as this is easier to read and prints better.

14. Consider zebra stripes: If you’re creating a spreadsheet with a lot of columns, it can sometimes be difficult for readers to match up data on the far right of the document with the categories listed on the left. Subtly shading alternate rows helps the eye to follow information across the page.

15. Name your worksheets: While this won’t impact the look of your spreadsheet, it certainly helps to keep everything well-organized and professional.

16. Exercise restraint! Once you start experimenting with Excel’s design features, you might be tempted to get a little too creative. Keep in mind that your main objective is to make your spreadsheets look professional and easy to read, so stay away from anything too elaborate.

17. Avoid repeating labels as much as possible: Make the table wide instead of long or long instead of wide. Make two levels of headers if necessary to prevent the need to repeat headings, using merge cells to create a broad heading, with more specific headings/labels in the columns below (or rows to the right).

The following pages show some examples of how you might format complex tables, keeping excess rows to a minimum. Review the tables in some of your textbooks for examples of professional appearing tables.

Hints 2 – 16 came from: http://www.techrepublic.com/blog/windows-and-office/20-excel-tips-for- creating-stylish-spreadsheets/

Hints 2 – 16 came from: http://www.techrepublic.com/blog/windows-and-offic…

Hints 2 – 16 came from: http://www.techrepublic.com/blog/windows-and-offic…

Response
Precontemplation: No, I haven’t thought about

taking up a new physical activity and have no plans to change.

Contemplation: No, but I am thinking about doing this sometime in the next 6 months.

Preparation: No, but I plan to start doing this sometime in the next month.

Action: Yes, I do this now, but it has been less than 6 months since I started.

Maintenance: Yes, I do this now, and have been doing this for 6 months or longer.

Number Percent Number

133 15.3% 108

129 14.9% 48 376 43.3% 256 107 12.3% 154 123 14.2% 302

Percent

12.4%

5.5% 29.5% 17.7% 34.8%

Pre- Post-

Hints 2 – 16 came from: http://www.techrepublic.com/blog/windows-and-offic…

Table 1. In a typical day, do you eat 2 or more servings of fruit? (n=868)

Response N

Pre-

Percent N

Post-

Percent

No, I haven’t thought about eating more fruits and have no plans to change.

No, but I am thinking about doing this sometime in the next 6 months.

No, but I plan to start doing this sometime in the next month.

Yes, I do this now, but it has been less than 6 months since I started.

Yes, I do this now, and have been doing this for 6 months or longer.

26 3% 15 2%

32 4% 296 34% 87 10% 427 49%

19 2% 189 22% 60 7% 585 67%

Hints 2 – 16 came from: http://www.techrepublic.com/blog/windows-and-offic…