WIXLIB

Graphing the results14. Now let’s create a visualization – a chart – of the results to graphically presentthe data. Starting in the top corner of the data, click and drag until the entiretable is selected.(Here’s a tip: You can click the first cell, then hold down the Shift key and thenclick the bottom right cell. Sometimes that is easier than clicking and dragging.)15. Click Insert PivotChart PivotChart (Note: this may look different ondifferent versions of Excel)16. Choose Clustered Column and click OK. You should now see a column chartsimilar to this one:Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data16

To see the complete label for each set of columns, grab each side of the chartand drag to enlarge the chart. You can also drag the chart vertically to adjust theheight of the columns. The chart should now look like this:Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data17

Discussion Questions:1. Which state has the highest number of surgical site infections from colonsurgery?2. Which state has the highest number of methicillin-resistant Staphylococcusaureus bloodstream infections?3. Which state has the highest number of surgical site infections from abdominalhysterectomy?4. For all the infections reported in the PivotTable, which state has the lowestnumber of infections?5. For all the infections reported in the PivotTable, which state has the highestnumber of infections?Other things you can do: You can use the PivotTable Field List to rearrange the fields later as needed byright-clicking the fields in the layout section, and then selecting the area that youwant, or by dragging the fields between the areas in the layout section. Click the Options and Design tabs of the PivotTable Tools that become availablewhen you click anywhere in a PivotTable, and then explore the groups andoptions that are provided on each tab. You can also access options and features that are available for specificPivotTable elements by right-clicking those elements. For detailed information about how to work with PivotTable reports andPivotChart reports, see Overview of PivotTable and PivotChart reports, Create ordelete a PivotTable or PivotChart report, and Pivot data in a PivotTable orPivotChart report on Office.com.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data18

Activity 3: Column Charts and HistogramsIn this tutorial you will learn the difference between a column chart and a histogram andlearn how to create them.Column charts and histograms initially look very similar. However, they are quitedifferent. A column chart plots each value in a data set as a vertical column A histogram is a graph that shows the frequency of values in a data set – in otherwords, how many times a particular value occurs -- and so histograms are veryuseful for showing how the data are distributed.1. Open the filecomp24 unit2 dataset prevalence and trends data tobacco use.xlsx[This dataset is derived from ]2. Take a few moments to look at the data. You’ll see the year and state namesalong the left side, then four categories of smoking status in the columnheadings across the top. Notice that the year goes from 2010 down to 1995,and that there are over 800 rows in the file.Run Descriptive Statistics3. Refer to the instructions for the tutorial on descriptive statistics. RunDescriptive Statistics on the 2010 data. For this video, I have already createdthe descriptive statistics. You will need to format the output for each MeanHealth IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data19

value so that they display as percentages. To do this, right click each Mean,then choose Format Cells, then Percentages, and then click OK.4. Your descriptive statistics for 2010 should look like this:5. Now run Descriptive Statistics on the 1995 data, formatting the Means asabove.6. Compare the two sets of data. Can you draw any conclusions yet?Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data20

Creating a Column ChartIn this step, you will graph the “smoke every day” data for all states for 2010. This is sothat you will be able to see the difference between a column chart and a histogram.1. Click in the cell labeled Year (cell A1) and drag down and to the right untilWyoming’s 2010 value of 14.90% is highlighted. Your screen should look like this:Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data21

2. On the Insert menu, click the column chart icon and then choose ClusteredColumn (Note: this may look different on different versions of Excel)3. Excel should now create a column chart similar to this one. Note that toimprove the readability of the chart, you may need to grab the right or left sideand drag to enlarge the chart.4. If the Y axis values are not displaying as percentages, right-click one of thenumbers on the Y axis and choose Format Axis. Under Number, change thecategory to Percentage. The Y axis will now show the percentages.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data22

5. Study the column chart. What can you tell from this chart? How difficult do youthink it would be to interpret this chart if it included all four smoking statuses forall the years in the spreadsheet?Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data23

Creating a Frequency Table and HistogramA frequency table shows how many times a value occurs in a data set, and a histogramis a graph of that data. Take a look again at the data for the “Smoke everyday” category.What we want to do is to set up groupings or “bins” for the values to fall into, such as 05, 6-10, 11-15, and so on. So how do we decide what those categories should be?1. First, let’s look again at the descriptive statistics for the “Smoke everyday”category. The Minimum value is .036 or 3.6%, and the Maximum is 0.232 or23.2%.2. So, we could create our categories, or “bins”, as 0-5, 6-10, 11-15, 16-20, and21-25. To do this, click in the first row under column H and enter the followingvalues: 0, 5, 10, 15, 20, 25. Your entries should look like this:3. Now you are ready to create the frequency table and the histogram. ChooseData Data Analysis and then choose Histogram. Click OK.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data24

4. The Histogram dialog box will display. For Input Range: enter the range of cells for 2010 Smoke Every Day (shouldbe C 2: C 55) For Bin Range: enter the cells where you put your categories (should be H 1: H 6) (Remember that you can click the drop-down at the right side ofthe field, then highlight the desired cells, and then click the drop-down againto redisplay the entire dialog box.) For Labels: we don’t need to check this option because we didn’t add acolumn label for the Bin column, and we didn’t use cell C1, which was thelabel for the Smoke Every Day column. For Output Options: click New Worksheet Ply to have the results show upon a new worksheet. (You may or may not see a value in Output rangedepending on the version of Microsoft Excel). Check Chart Output to have Excel automatically graph the results as ahistogram. Your screen should look like this: Click OK.5. Excel will create a new worksheet called Sheet1 and will display this worksheet.You should see the following output (recall that you can reformat the axes todisplay as percentages)Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data25

To see more levels in the Frequency axis, such as 25 and 35, drag thehistogram down to expand it.6. Now create a frequency table and histogram for the following sets of data (Tip:you will need to run Descriptive Statistics and adjust your bins): Smokes Every Day data for 1995 Never Smoked data for 2010 Never Smoked data for 19957. Compare the four histograms. Comparing 1995 data to 2010 data, whatconclusions can you draw about smoking in the United States?Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data26

Activity 4: Testing for Independence with Chi Square1. A Chi square test for independence tests to see if there is a relationshipbetween two categorical (nominal) variables. Another way to state this iswhether the two variables are independent of each other. In this exercise, wewill answer the question, “From 1997-2014, is there a relationship betweenincome and heart disease for people 55-64 years old?”2. Open the file comp24 unit2 dataset chonic conditions.xlsxThis dataset gives data on persons reporting specific chronic diseases on theNational Health Interview Survey. [This dataset is derived from HealthConditions Chronic conditions lders.aspx].3. Take a few moments to look at the data. You’ll see the parameters that wereused to generate the report on line 4, and then on line 6, the different chronicdiseases, such as heart disease, stroke, and arthritis. Along the left side areage categories, subdivided into income levels of Poor, Near poor, and non-poor.There are also numerous comments, indicated by the red triangles, that displaymessages when the cursor is hovered over them.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data27

Set up a table of observed valuesSince we want to look at the relationship between income level and heart disease forpeople between 55-64 years old, we need to set up a table that has columns for IncomeLevel, Has Heart Disease, and No Heart Disease. This will be our Observed Values.1. Copy the values for Income and Heart Disease for rows 37, 38, and 39 to ablank area of your worksheet. This gives us the number per 100 patients whoreported that yes, they had heart disease:2. Now we need to manually calculate how many patients per 100 who did nothave heart disease. In the blank column to the right, subtract each value from100.3. Add a title above each column; the first column should be Income, the second“Heart Disease per 100”, and the third column “No Heart Disease”.4. Above the Income column, add the title “Observed Values”.5. Your work area should now look like this:Set up a table of expected valuesThe Chi square test needs to have a range of expected values to compare the observedvalues against. For the purposes of this exercise, we will use the data for All patientsbetween 55-64 years old on row 36, which has a value of 15.8.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data28

6. Copy the cell with the value All and the adjacent cell with the value 15.8. Thisgives us the number per 100 patients, of all income levels, who reported thatyes, they had heart disease.7. Paste this section a few lines below your new Observed Values area of yourworksheet.8. Now we need to manually calculate how many patients per 100 who did nothave heart disease. In the blank column to the right, subtract 15.8 from 100.9. Since we have three rows of data in our Observed Values area, we have tohave three rows in our Expected Values area. Copy the three cells “All”, “15.8”,and “84.2” to two additional rows.10. Add the title “Expected Values”.11. Your work area should now look similar to this:Run the Chi square test for independence12. Click in an empty cell underneath your table of expected values.13. On the menu bar, choose Formulas Insert Function. The Insert Functiondialog box will display. Type in CHI in the search box and click Go.Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data29

14. A list of functions whose names begin with CHI will be displayed. SelectCHISQ.TEST and click OK.15. You will now see the Function Arguments dialog box for the CHISQ.TESTfunction. Actual range: click in the Actual Range field, then click the cell with the 23.8value and drag down and to the right until all six data values for the ObservedValues are highlighted. The cell names will be entered in the field. (Note that yourcell names may be different, depending on where you placed your data in yourspreadsheet.)Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data30

Expected range: click the Expected Range field, then click the cell with the first15.8 and drag down and to the right until all six data values for the ExpectedValues are highlighted. The cell names will be entered in the field. Again, yourcell names may be different. Click OK. The number 0.023147 should appear in the cell.Interpreting the DataIn this case, the result reported as 0.023147. If the value is less than or equal to ( ) 0.05, then there is a statisticallysignificant relationship between income level and heart disease. If the value is greater than ( ) 0.05, then there is not a statistically significantrelationship between income level and heart diseaseAnswer the following questions Is there a statistically significant relationship between income level and heartdisease? Are heart disease and income level independent of each other?Health IT Workforce CurriculumVersion 4.0/Spring 2016Health Care and Data AnalyticsWorking with Data31

Downloading Excel Analysis ToolPak The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use the Analysis ToolPak in Excel, however, you need to load it first. . StatPlus