Excel Assignment

HSCI 273 30 points EXCEL SPREADSHEET EXERCISE #1

HINTS: While completing this assignment, think about X and Y axis placement for the spreadsheet. Why is it important to set up a spreadsheet properly at the beginning? Although you will be able to graph many things, will the graphs always make sense and be appropriate? Think about these hints as you proceed through this exercise. Use the Help menu if you need to!! READ AND FOLLOW DIRECTIONS….look for the fine points. 1. Research: Locate county health indicator data for Orange, San Diego, Riverside and San Bernardino counties

regarding the following areas: • Coronary heart disease deaths • Diabetes deaths • Female breast cancer deaths • Suicide death rates • Firearm related deaths • Gonorrhea (male and female) case rates • Infant Mortality (Asian/PI, Black, Hispanic, White)

Look up adjusted death rates or the crude case rate for each county stated above. You also need the age- adjusted rate or the crude case rate for the state of California, as well as the National objective. Use the most current data available at this site: URL http://www.cdph.ca.gov/programs/ohir/Pages/CHSPCountySheets.aspx Ponder: Is this census or health data?

2. Research: Using www.census.gov, click on Quick Facts and click on California. Select each of the four counties listed in #1 above. Find:

• Total Population (2014) • Race (list White, Black African American, American Indian, Asian, Native Hawaiian, Two or more Races) • Median Household Income • Percent living below poverty • Percent having completed high school only • Percent having completed a bachelors degree Ponder: Is this census or health data?

3. Download the death by zip code data spreadsheet: http://www.cdph.ca.gov/data/statistics/Pages/DeathProfilesbyZIPCode.aspx

• Obtain information for female and male deaths in zip code 92410 Ponder: Is this census or health data? Once you have gathered all of this data you will put it into an excel spreadsheet. You need to make sure that it is formatted to make the data easy to read and understand. Include gridlines so the data is clear to follow and have the spreadsheet set up in such a way that allows the viewer to compare data between counties.

4. Create an Excel Workbook:

HINT: Always use descriptive titles on every chart and sheet. Titles should tell who, what, when, where. A. Create Data Sheets: Now that you have researched all of the information above, you will have to decide 1) which information is census data and which information is health data 2) which information will go on the X axis (horizontal) and Y axis (vertical).

*On one worksheet you will have census data and on another sheet you will have health data.

B. Alter One of Your Data Sheets: Choose one of the worksheets you created in Step A, use the Format

menu and cells. Select one or two columns: Use Font, Alignment, and other tools. Change the text direction of one column header.

a. Use fill colors (not text color) and a pattern in a couple of cells. b. Change the column width. Align cells left, center, right. c. Give the chart a title in Layout under chart tools menu. d. Use the Wrap text feature so that the county name is on one line and the word “county” is on the second line—so you have to include county with each name. e. When you print out your worksheets, use a highlighter to make sure to point out where these changes were made.

5. Create Charts: HINT: If you print with black ink, you should use patterns and not colors for the charts.

1. Create a Stacked Column Graph for the case rate of gonorrhea for each county. Be sure to include both the male and female data and not the combined total. Make the lines different colors/patterns. Include a title with font large enough so that if you copy and paste this into a letter, the title is readable. Include the title at the top. Include the values on each column. Put a legend on the bottom. Save this as a new sheet. 2. Create an Exploded 3-D Pie Chart for the deaths by female and male in ZIP code 92410. Insert the percentage on each portion. Include the title on the bottom and the legend on the side. Save this as a new sheet. 3. Create a Clustered Bar Chart of Coronary Heart Disease Deaths for the each of the four counties. Put the legend on the right and title on top. Include the values on the bars. Use different colors/patterns for each bar. Save this as a new sheet. 4. Create an Exploded 3-D Pie Chart for San Bernardino County to show race and ethnicity. Choose the exploded 3-D pie. Put values on the slices. Include a title at the top and the legend should go on the bottom. Save this as a new sheet. 5. Create a Clustered Column Chart for the percent living below poverty in each of the four counties. Be sure to make each column a different color/pattern. You should include the numbers/data on this chart in a box at the bottom of the graph. Look in the Chart Quick Layouts to find this. Place a title on the top. Save this as a new sheet.

PRINT THE FOLLOWING TO HAND IN: 1. Two (2) data sheets:

A. Print grid lines on each data sheet only for the data not for the whole page. Set the print area. Print the health data and the census data worksheet with gridlines. Look at “Page Layout” in menu before printing.

2. Five (5) charts:

A. Stacked Column Graph for the case rate of gonorrhea for each county B. Exploded 3-D Pie Chart for the deaths by female and male in ZIP code 92410 C. Clustered Bar Chart of Coronary Heart Disease Deaths for the each of the four counties D. Exploded 3-D Pie Chart for San Bernardino County to show race and ethnicity E. Clustered Cone Column Graph for the percent living below poverty in each of the four counties

Do NOT sit idle. If you are at a stopping point, go through every menu item and see what the options are. Change the properties for the document. Freeze and unfreeze the first row and column after you enter some data. Change the name of the sheet and chart tabs on the lower edge of the excel view.