Creating a Column Chart

The y-axis in the chart—labeled Observations—was supplied automatically by Excel from the number of observations in each age group. The two labels Age Categories and Observations were entered in the Excel sheet in cells D1 and E1. The numbers on the x-axis represent the Bin values in cells D2:D7. We included these in the chart by selecting cells D1 through E7 and clicking the Column icon on the Insert ribbon. A two-dimensional column chart is then chosen, and an initial column chart is created. However, the initial column chart needs to be formatted to look like the one in Figure 4.7. Right-click the column chart and choose the Select Data option, and the Select Data Source dialog box will appear (see Figure 2.18). Within this dialog box select Age Categories in the Legend Entries area and click the Remove button. Also, in the Horizontal (Category) Axis Labels area click Edit; when the Axis labels range box appears, highlight cells D2 through D7 and click OK. This deletes the Age Categories as data to be graphed and adds the proper x-axis scale. The titles Graph of Age, Observations, and Age Categories can be modified by using any of the style buttons on the Design ribbon (e.g., adding a title for the y-axis).

In any chart, such as the one shown in Figure 4.4, the y-axis is always considered to be the vertical axis on the left and the x-axis is always considered to be the horizontal axis on the bottom.

The view of the data in Figure 4.7 clearly shows the tail to the left that makes these data skewed to the left. It also shows that the largest single category is the age group with the x-axis value of 77.0. It should be remembered that the x-axis designation 77.0 actually refers to all observations with ages between 67.5 years and 77.0 years. This characteristic of Excel, to use the largest value in a frequency as the Bin value, can be confusing when displayed in a chart such as that shown in Figure 4.7. Because the x-axis values are supplied by the creator of the chart, the x-axis labels can be made more descriptive.

Figure 4.8 shows part of the spreadsheet for the Age frequency distribution. The figure includes the graph of the frequency distribution with new x-axis designations. You put these into the chart by first entering them as a set of categories in cells E9: E14. Change the x-axis in the chart by right-clicking the column chart and choosing the Select Data option. The Select Data Source dialog box will appear (see Figure 2.18). Within this dialog box select the Horizontal (Category) Axis Labels area and click Edit; when the Axis labels range box appears, highlight cells E9 through E14 and click OK. This makes the x-axis labels much more descriptive of what is actually in those categories and what the bars in the graph actually represent. It is worth mentioning that the chart shown in Figure 4.8 is the same chart (in the original spreadsheet) as that shown in Figure 4.7. This demonstrates an extremely useful and important characteristic of the Charts function: When you change the data or labels that make up the chart, the corresponding changes appear in the Excel chart. This applies whether different data or labels are selected or when changes to the actual data or labels are made.

It is important to mention that the categories shown in Figure 4.8 are still not exactly right. The range 48.5 to 58.0, for example, is actually any age greater than 48.5 to 58.0, because if there were an age recorded as 48.5, it would be in the <=48.5 category. This is true in every other Age category as well.

Various Charting Types within Excel

Data can be displayed in a number of other ways using the Charts function. In fact, Excel offers 14 standard types of graphs and 20 of what Excel calls custom types. All of the standard types have a number of subtypes. For most of the work in this book, the simple column chart (frequently called a histogram) is all that will be used. However, it is also useful to look at four other of the charts that Excel can produce.

Creating a Line Chart

The first alternative chart type is the line chart, a column chart in which the presentation is made with a single line rather than with a number of columns. The data charted in Figure 4.9 are the same as those charted in Figures 4.7 and 4.8, but the columns have been changed to a single line. This is done by selecting the chart in Figure 4.8, selecting the Change Chart Type icon in the Type group on the Design ribbon, and then choosing the Line chart option. It is not uncommon to make the addition to the line chart by dropping the line to the x-axis at each end. This was done by inserting a zero cell at both ends of the frequency distribution and a blank cell at each end of the Age categories that form the x-axis values. Then, both the zero values and the blank cells were included as part of the chart. The result is the data depiction in Figure 4.9.

Creating a Bar Chart

A second chart of interest is the bar chart, as shown in Figure 4.10. The bar chart shows exactly the same information as that shown in the column chart, but it shows it in a different orientation. To put the data into this chart, it was necessary only to select the chart in Figure 4.8, choose the Change Chart Type icon in the Type group on the Design ribbon, and then choose the Bar chart option. Notice that with the bar chart the smallest charted value is always at the lower end of the vertical column.

Creating a Pie Chart

A third chart that is useful to consider is the pie chart. The pie chart presents data as the pieces of a pie, each piece representing its proportional part of the whole. The notion that each piece represents its proportional part of the whole is a significant factor in the use of a pie chart. The pie chart assumes that the material depicted represents the entire set of information and, furthermore, that all the pieces accumulate to the total. This is true with the Age data. Each frequency category represents a part of the entire frequency distribution, and all taken together add to all 100 observations. The pie chart can be created from the original chart shown in Figure 4.8 by selecting the chart again, choosing the Change Chart Type icon in the Type group on the Design ribbon, and then choosing the Pie chart option. The result, after some manipulation of labels and axes, is shown in Figure 4.11.

It is important to reiterate that the pie chart, by its very nature, implies that 100 percent of observations of any type are being included in the graph. This is further depicted by the percentages that are shown in the chart in Figure 4.11, which add to 100 percent. A pie chart should never be used to depict a total amount and its subcomponents together, or part of a total amount.

Creating an XY(Scatter) Chart

To this point, the discussion has focused on looking at the frequency distribution of a single variable at a time. Excel offers an entirely different type of chart that can be very useful in examining the relationship between two separate variables at the same time. This is what Excel calls the XY(Scatter) chart. The XY chart requires two separate variables. The XY chart of Age and LOS (length of stay) is shown in Figure 4.12. Several points about this chart should be mentioned. First, this chart is not based on frequencies in categories, as the other charts discussed here have been, but on the actual values of the two variables. The horizontal axis, or x-axis, represents the values of the variable Age. The lowest value of Age in the data set is 39 and the highest value of Age is 99. The vertical axis, or y-axis, represents the values of the variable LOS (length of stay). LOS ranges from the shortest length of stay of 1 day to the longest stay of 16 days.

Excel always treats the left-most variable in the spreadsheet as the x-axis variable when constructing an XY chart and the right-most variable as the y-axis variable. The variables do not have to be in contiguous columns to construct the XY chart, but the data must begin in the same row in order to successfully construct an XY chart. Each diamond in the XY chart represents one observation. For example, the left-most diamond in the chart represents the single person who was 39 years of age and had a hospital stay of one day. The only diamond above the line marked 15 on the vertical axis is the single person who had a 16-day hospital stay. The person staying 16 days appears to have been 83 years old. The person's age is read from the horizontal axis. Each of the other points in the chart is interpreted in the same way, although one would not count on the chart to know the actual values. The chart works more to provide a general view of what the entire data distribution looks like. This information is valuable, particularly in deciding whether regression analysis (discussed in Chapters 11 to 13) will be appropriate to the data.

Creating a Cumulative Frequency Distribution

A cumulative frequency distribution begins with the smallest Bin value. It then accumulates observations so that the first category contains only the observations in that Bin, the second category contains observations in both the first and second Bins, the third contains observations in the first three Bins, and so on. Figure 4.13 shows a cumulative frequency distribution for the variable Age in cells F2:F7. The cumulative frequency was calculated by making cell F2 equal to cell E2 and then adding each successive cell in column E to the current total in column F. For example, cell F3 is =F2+E3 and cell F4 is =F3+E4.

The formulas used to construct the cumulative frequency are shown in the formula view of Figure 4.13, which is given in Figure 4.14. Cell F2 in this view shows that it is simply equal to E2. Cell F3 shows that the number in that cell was developed with the formula =F2+E3. Once =F2+E3 has been entered into cell F3, the remainder of the cells in column F can be entered by copying cell F3 down. This can be accomplished by placing the cursor in the lower right corner of cell F3 and either dragging the cell references down to cell F7 or double-clicking the lower right corner of cell F3. As the formula moves down each cell, it becomes the correct formula for that cell by virtue of relative cell referencing. It is extremely important to remember that, in general, you need enter any formula into Excel only once. You are doing way too much work if you enter the formulas for cells F3:F7 separately in each cell.

Creating a Percentage and Cumulative Percentage Distribution

Cells G2:G7 and H2:H7 in Figures 4.13 and 4.14 show the percentage distribution and the cumulative percentage distribution for the Age frequency data. Because the original data involved exactly 100 observations, the percentage values are the same as the actual frequencies. In general, this will not be the case. In any event, the formulas for the percentage distribution are clearly different from the formulas for the actual frequencies (cells E2:E7), which rely on the =FREQUENCY() function. The formulas for the percentage distribution (column G) rely on the dollar sign ($) convention (cycle through with function key F4) to fix cell F7 as the cell by which each value in column E is divided. Again, it is necessary only to enter the formula =E2/$F$7 (F7 is the cumulative total value) into cell G2. Then the formula can be copied into each of the other five cells in column G. The relative reference to each cell in E in the numerator changes as the formula is copied down column G, but because of the dollar signs before the F and before the 7 in the denominator, that reference does not change.

The formulas for the cumulative percentage distribution (H2:H7) are the same as the formulas for the cumulative frequency distribution. Because of this, it is necessary only to copy the range F2:F7 and then paste the copy into cell H2. That will enter all the values for the cumulative percentage distribution in H2:H7. It should be mentioned that the actual values that will appear in both G2:G7 and H2:H7 will be decimal values until they are reformatted to percentages. This can be done by highlighting G2:H7 and from the Home ribbon choosing the Cells tab ⇨ Format menu ⇨ Format Cells menu and choosing Percentage on the Number tab, or by highlighting G2:H7 and clicking the % icon on the Number tab in the Home ribbon.

It may be useful to look at a graph that includes both the actual frequency and the cumulative frequency. Figure 4.15 shows such a graph. The actual frequency is in light gray, and the cumulative frequency is shown in dark gray. It should be mentioned that a number of things were done to this graph to make it look as it does (and almost all graphs need some modifications to make them look good when they are first created).

Modifying a Chart

When a graph is first generated, it frequently is too narrow vertically to provide a very good view of the comparative length of the columns, so this graph was stretched out both vertically and horizontally. (After clicking on the chart, grab one of the black squares on the chart border to resize it.) The data labels, Freq and Cum Freq, were initially given as Series 1 and Series 2. These were modified by changing and/or including the cell references for the names in the data series. This is accomplished using the Select Data Source dialog box. Once the name has been changed, the new name will appear in the Series window. Series 2 was renamed Cum Freq in the same way.

The Age categories shown in Figure 4.15 were originally given in too large a font to be displayed entirely in the graph. They were also displayed at an angle because of their larger size. To put them into the form in which they now appear, it was first necessary to right-click anywhere in the set of numbers that appeared as the Age categories (the horizontal scale) to invoke a pop-up menu that allows you to access areas such as Font or the Format Axis dialog box. After that Font option was selected, the font size was changed to 9 from 12. Then the Format Axis dialog box was chosen, the Alignment tab was selected, and the cursor was used to move the diamond on that tab from the horizontal and back again. That produced the Age category format, as shown in the figure.

The initial graph, before reformatting, also had a vertical scale that went to 120. As there are only 100 total observations, and in order to indicate that the last dark gray column represented all of the observations, the vertical scale was changed so that it would go only to 100. This was done by right-clicking anywhere in the numbers on the vertical scale to invoke the Format Axis dialog box for the vertical axis. After left-clicking that menu, select the Axis Options tab and change the maximum to 100. The graph as it appears in Figure 4.15 is the result of all these operations.

The total number of changes and modifications that can be made to a graph to serve the purposes desired is extremely large—far beyond what can be described in this text. The plot area itself can be reformatted in a number of ways, the colors of the columns can be changed, and many other modifications can be made. The only way to get a good notion of the modifications that can be made in an Excel graph is to create a graph and experiment with the various options. Sooner or later, you will hit on a series of style changes that will best serve your purposes.

Skewed Distributions

The graph of Age shown in Figure 4.7, as well as subsequent graphs, is skewed to the left because the distribution shows the characteristic tail on the left side of the distribution. Data distributions may also be skewed to the right. In the data file under discussion thus far in this chapter, LOS (length of stay), Charges (total hospital charges), and Medicare (Medicare payments) are all skewed to the right.

Figure 4.17 shows the distribution of Medicare payments in six categories. It is clear that this distribution has a tail that moves off to the right, thus making it right-skewed. In general, it is more common to encounter distributions that are right-skewed than distributions that are left-skewed. Costs of health services (office visits, hospital stays, emergency room visits, or any other categories of cost) are likely to be skewed to the right. This is because most costs will be more or less similar, but there will be a few encounters with very high costs. This produces a distribution that is skewed to the right. Disease or morbidity patterns are similar.

The State of the World's Children 2001 ([2000]) provides one example of a distribution that is skewed to the right. UNICEF reports data on infant mortality for countries of the world at www.unicef.org/sowc01/tables/#. According to UNICEF, infant mortality for 149 countries of the world with more than 1 million inhabitants ranged in 1999 from a high of 182 deaths per 1,000 live births (Sierra Leone) to a low of 3 deaths per 1,000 live births (Sweden and Switzerland). But the distribution of infant mortality by countries of the world is skewed to the right, as demonstrated in Figure 4.18.

As Figure 4.18 shows, more than 70 countries of the world have infant mortality rates of 33 or less. The number of countries with infant mortality rates in each of the higher categories decreases monotonically to the 153-to-183 deaths category. There are exactly five countries in this category. This distribution is decidedly skewed to the right.

Normal Distributions

The normal distribution is one that is skewed neither to the right nor to the left but, rather, has tails of approximately equal length. Exact characteristics of normal distributions will be discussed in Chapters 5 and 6. But for the current presentation, the notion that the normal distribution is skewed in neither direction is sufficient. Approximately normal distributions are found in many health care–related types of data. The body temperature of healthy human beings, for example, is probably normally distributed around 98.6 degrees. Not every person's body temperature is exactly 98.6 degrees; body temperature varies from that normal temperature by a fraction of a degree or more. This variance is probably as likely to be below 98.6 as it is likely to be above 98.6. Average height of adults is another example of data that are generally normally distributed with a more or less equal number of persons on each side of the distribution.

Data on infant mortality for the world are skewed to the right. But data on infant mortality for the United States by state more closely approximate a normal distribution. Infant mortality in the United States ranged in 1998 from a low of 4.4 deaths per 1,000 live births (New Hampshire) to 10.2 deaths per 1,000 live births (Alabama) (U.S. Census Bureau, [2001], p. 89).

The graph of infant mortality by state in the United States is shown in Figure 4.19. This graph clearly shows that the bulk of states have infant mortality rates in the center of the distribution rather than at the left side. The number of states that have infant mortality rates at each end of the continuum is, while not exactly equal, certainly in the same order of magnitude. The infant mortality rate by state in the United States is much closer to normal than that for the same measure by countries of the world.

Uniform Distributions

Although uniform distributions are not often found in health statistics, it is still useful for comparative purposes to show an example of one. A uniform distribution is one that has approximately equal numbers at each interval. To give an example of a uniform distribution, the random number generation selection in the Data Analysis add-in was used and the Uniform distribution option was selected. This option allows one to generate a random number between any upper and lower limits. One hundred random numbers were generated in the range zero to six. The random number generation add-in produces numbers to nine decimal places. Then the =FREQUENCY() function is used to put these numbers into the six categories—1, 2, 3, 4, 5, and 6. This is a reasonable simulation of the roll of a fair die 100 times. The result is shown in Figure 4.20. An actual uniform distribution (or a simulated one as in Figure 4.20) will not look exactly like the example in the lower right quadrant of Figure 4.16, but it will look approximately like that example.