Usually we are interested in relative frequencies, since they put the raw count in perspective. However, if the sample size is particularly small, relative frequencies can be misleading.

The relative frequencies of the sample are clear: 33 percent of the samples were female and 67 percent were male. This seems to suggest that about 33 percent of all evaluations were submitted by females, 67 percent by males. However, since the sample size is so small, these figures are likely to be incorrect. Thus, if the sample size is small, relative frequencies might convey a false sense of certainty.

Perhaps a reasonable solution is to state the relative frequency as a ratio, not in percentage. In other words, one out of three evaluations was female and two out of three were male.

To create a relative frequency table, we first create a standard frequency table; as before, then we convert each frequency into a relative frequency by dividing it by the sample size. Figure 2.9 shows the resulting table, using 10 categories, of MLB salaries in 2011. We added a column containing the relative frequencies by dividing each frequency by the sample size n = 843. Now it is easy to see that approximately 6.5 + 64.8 + 12.9 + 5.8 = 90% of MLB players made less than $13 million in 2011.

Note that a frequency histogram and a relative frequency histogram will have the exact same shape. The only difference between the two would be the scale on the (vertical) y-axis.

Figure 2.9 Relative frequencies of MLB salaries in 2011

Cumulative Frequency Histograms

Another frequency that is often used is the cumulative frequency. It is defined as the sum of the relative frequencies up to the given bin.

To compute the cumulative frequency for a row, we add the relative frequencies up to and including that row, or equivalently we add the current relative frequency to the prior cumulative frequency (see Table 2.3).

We already saw that relative frequencies translate to probabilities. Cumulative frequencies, on the other hand, will be useful to find median, quartiles, and percentiles (see Chapter 3).

Excel provides a number of additional charts as well as variations on existing types. It is easy to experiment so feel free to check out other types of charts.

Bending the Rules: Lying or Exaggeration

Using graphical data representation provides a great opportunity to visualize data so that it conveys a particular point of view. This is not cheating; it is simply using some visual aids to make your data appear to support one particular point of view over another without actually changing the data. Table 2.4 shows, for example, some data of how much different states spent per student in dollars in 2013.

It is easy to see that of the states listed, New Jersey (NJ) spends the most per student, about twice as much as states like Arkansas (AR) or Mississippi (MS). The difference between NJ and AR is pretty clear (see Figure 2.10).

Now suppose we want to give a presentation in which the state of AR is supposed to look reasonably good as compared to the state of NJ. We could create a bar chart that minimizes the visual differences in state spending by using a particularly “large” scale on the y-axis. See Figure 2.11.

We are also de-emphasizing the empty space that results in choosing a large y-scale by placing the chart title into that area. In this chart it is still clear that NJ spends the most per student—after all, we cannot change the actual data—but the difference does not look quite so stark any more. As another option, we could remove the horizontal gridlines to make it harder to see exactly how much money the different states actually spend.

Figure 2.10 Standard bar chart representing money spent per student in 2013

Figure 2.11 Spending per student, de-emphasizing differences between states

Figure 2.12 Spending per student, emphasizing the difference between AR and NJ

Now let us try the opposite: we want to give a presentation in which the state of AR looks bad as compared to the state of NJ. Thus, we pick a scale on the y-axis that makes sure that the difference between AR and NJ appears as large as possible. In particular, we choose a y-scale that starts at 6,000 and ends at 17,600 instead of the standard values. See Figure 2.12.

We can also pick an “aggressive” color (such as red) for the AR figure and a “calm” color (such as green) for NJ, emphasizing the fact that we want to represent AR as “bad” and NJ as “good.” In this chart AR indeed looks pretty bad compared to NJ—in fact, it seems as if NJ spends many times more money per student than AR—but we have not changed the actual data values.

All three charts represent the same data and are perfectly valid. Yet visually they tell different stories. There are many other tricks that can be used to represent data in such a way as to support one particular point of view without outright changing the data.

If you were a health official in Dallas, you might want to use this data to try to get people in your region to vaccinate against the H1N1 flu or to encourage a government agency to fund prevention and treatment programs in Dallas. Thus, you are trying to create a chart that emphasizes the number of cases in Dallas versus the other regions so that your citizens are motivated to get vaccinated or that funding is approved. Figure 2.13 shows a few suggestions.

Try this: check your local newspaper or online news source to find some charts. See if these charts try to promote any particular point of view or if they are relatively neutral.

Using Microsoft Excel

Usually we use Excel for help with some calculations only. This is relatively easy and needs no further explanation. However, Excel also includes some more complex procedures useful for statistics; we will explore some of them in this section.

Figure 2.13 Tricks to highlight a particular data point without changing the data

Creating Charts

We have already seen bar charts and pie charts to summarize our data. Such charts can be created in Excel with just a few clicks. Note that the following instructions are for Excel 2013 but other versions of Excel offer similar capabilities.

We have only six categories for our data, so neither a bar chart nor a pie chart can be excluded. We will create both to see which one seems more meaningful.

• Enter the data into an empty spreadsheet in Excel.
• Use the mouse (or cursor keys while holding SHIFT) to select the cells in the first two columns, including the first row containing the variable names; ignore the third column for now.
• Select the “Insert” ribbon. You should see an icon for “recommended chart” as well as icons for specific chart types. It is usually a good idea to check the recommended chart. In our case, though, we know which chart type we want, so we directly click on the pie chart icon and select the “3D” subtype.

While you hover over a particular chart type you will see a preview of the chart. In our case the 3D pie chart for the variable poverty is shown in Figure 2.14 on the left. We repeat the procedure to produce a 3D column bar chart, shown on the right in Figure 2.14.

The pie chart is not very helpful. It is difficult to grasp which slice represents what state and it is difficult to see the actual poverty rate for each slice. In addition, Excel recommends pie charts if the various category values add to 100 percent. This is not the case, so this chart type is out. The bar chart in Figure 2.14 on the right side has more potential but at the moment it is not looking its best.

• Click on the chart title “Poverty” and press Delete. The title will disappear and the chart will grow slightly.
• Excel offers some “styles” on the “Insert” ribbon that can further improve the look of a chart. We pick, for example, the third option called “style 3” to get a nicely formatted, readable chart as shown in Figure 2.15.

To create a chart for the “crime” variable we first switch the second and third columns with each other via cut and paste—for simple charts it is easiest to make sure that the data and the labels are next to each other. If you select all three columns, Excel will recommend a “stacked” chart, which is not appropriate for this data. Alternatively, you could use the cursor keys together with CTRL to select the non-adjacent columns “State” and “Crime.” We leave the details to you.

For additional help and information, see “How to use the Chart Wizard,” available at https://support.microsoft.com/en-us/kb/304421

Excel’s Histogram Tool

The Analysis ToolPak provides a convenient tool to create histograms for numerical variables.

Figure 2.14 Poverty data for New England states in a pie chart (left) and a bar chart (right)

Figure 2.15 Nicely formatted bar chart for poverty data

Here is a quick walk-through of this procedure:

• Start Excel and enter the preceding numbers, all in one column. You do not need to enter a title or anything else, just the numbers in one column, one number in each row.
• Bring up the “Data Analysis ...” dialog (remember, it is available on the “Data” ribbon). If you do not see this item, you must first install the “Analysis ToolPak” as described in Chapter 1. You will see a dialog box listing all procedures available in this Analysis ToolPak. Highlight the entry “Histogram”; then click “OK.”
• Next, enter the options for a (frequency) histogram, including the location of the data to use and, optionally, the categories (bin ranges) that you want to define. See Figure 2.16 for details.

To define the input range, you can use the “cell selector” icon next to the “Input Range” field. Click it and use the mouse or cursor keys to select the appropriate cells by highlighting them. Click the “Return” icon to return to the original “Histogram” dialog box. Leave the “Bin Range” empty for now so that the tool will automatically pick the bins for the data. Make sure to check “New Worksheet Ply” as your output options, check the “Chart Output,” and click OK. Note that if your first cell contained a variable label instead of the first value, you would need to check the “Labels” option as well. The resulting histogram is shown in Figure 2.17.

Figure 2.16 Options for the histogram procedure in Analysis ToolPak

As usual, we can now customize our chart by double-clicking on its components to replace the various titles by more meaningful names, and removing the “Frequency” label. In this example Excel determined the categories for our numerical variable (the “bins”) automatically.

• Category 1 includes all numbers less than 72 and includes one measurement.
• Category 2 goes from 72 to 78.6 and includes four measurements.
• Category 3 goes from 78.6 to 85.2 and includes nine measurements.
• Category 4 goes from 85.2 to 91.8 and includes four measurements.
• Category 5 goes from 91.8 to 98.4 and includes six measurements.
• Category 6 includes everything above 98.4 and includes one measurement.

If we want to define the bin boundaries manually, we would add the numbers representing the bin boundaries in increasing order somewhere in a column and select them by clicking the cell selector icon in the “Bin Range” field.

Figure 2.17 A histogram with automatically generated bins

We need to determine the boundaries of the bins so that we end up with exactly four bins. We can use Excel’s =min(RANGE) and =max(RANGE) functions to determine the smallest and largest data point. Then, if we use bins with width of (max − min)/(number of bins), we will end up with the right number of equally spaced bins:

min = 72, max = 105, bin width = (105 − 72)/4 = 8.25.

Now we add a column to the Excel table where we define the bin boundaries:

min + width = 80.25

min + 2 * width = 88.5

min + 3 * width = 96.75.

Note that we need three bin boundaries to define four bins:

less than 80.25

between 80.25 and 88.5

between 88.5 and 96.75

bigger than 96.75.

Finally, start the histogram procedure from our Analysis ToolPak. Define the data range as usual, but also define as bin range the three bin boundaries we just computed. The resulting histogram will now have four bars, as desired.

Students frequently have trouble using the histogram tool with a given number of bins, so here is one more example.

We first compute the minimum and maximum data values and then use them to define the bin widths for our bins. Finally we create 9 bin boundaries to define our 10 bins, letting Excel do the actual computations (see Figure 2.18).

With these boundaries in place, start the histogram tool as usual. Enter the range for the salary data C2:C19544 and use the boundaries we just computed as bin range (H6:H14). Make sure the option for “Chart Output” is checked to generate this histogram chart in addition to the table. The resulting histogram should look like the one in Figure 2.8.

Excel’s Pivot Tool: Charts for Categorical Data

Often one would like to know the frequency of occurrence of values for a variable in percentage. This is similar to a frequency histogram we studied earlier, but a histogram only applies to numerical variables, while the procedure outlined in this section applies to categorical variables.

Figure 2.18 Computing bin boundaries for MLB data

Loading this data into Excel, we see in Figure 2.19 that the column of interest is column E titled “Race.” However, that column represents a categorical variable (ordinal or nominal?), so we cannot compute a frequency histogram.

Before we figure out how Excel can generate the desired data automatically let us do it by hand. Inspecting the data we see that there are five categories: White, Black, Hispanic, Pacific Islander, and Other. We can manually count how many people are contained in each category (see Figure 2.20).

Our manual procedure worked because we did not have that much data. For large data sets we need to figure out an automatic procedure to generate the appropriate table. Fortunately, Excel has just such a procedure, called a Pivot Table. The pivot tool is found as the first button of the “Insert” ribbon. It looks slightly different depending on your version of Excel but the differences are pretty minor. To use the pivot tool:

• Load the preceding spreadsheet into Excel and click anywhere outside the data area, for example, below the last row of data (otherwise the pivot tool may be disabled).
• Select “Insert | Pivot Table” and choose the entire data table for the Input Range field, using the by now familiar range selector. Make sure to pick the entire data table, including the first row containing labels. Excel should by default have selected the entire table already for your convenience.
• Choose to put the resulting pivot table into a new spreadsheet and click “Okay” to generate the pivot table, which will initially be empty.

Figure 2.19 Excerpt of data from a student survey

Figure 2.20 A frequency table generated manually

You will see a “potential frequency table” containing labels such as “Drop Row Field Here,” “Drop Column Fields Here,” and so on, but no data values are yet contained in the table (see Figure 2.21). There will also be a list containing the available variables from your data, in our case “id,” “Sex,” “Weight,” “Height,” “Race,” and so on. You can “drag and drop” these variables onto the various slots in the table to create a variety of useful tables for data analysis.

• Drag the variable “Race” from the field list into the “Drop Row Fields” area of the table. Your table will adjust, showing you all available “Race” categories but as for now no frequencies (counts).
• Next, again drag the variable “Race” from the field list, but this time drag it to the “Drop Value Fields” area in the middle.

Figure 2.21 An empty pivot table with a list of named fields

Figure 2.22 A pivot table showing the counts in the race categories

You will now see the counts of how many occurrences fell inside each race category, which of course will turn out similar to the one we created manually before in Figure 2.17, except this time it includes the “blank” category (and the order may be different). See Figure 2.22 for the finished pivot table.

See if you can eliminate the “blank” response row. (Hint: Maybe you can find a drop-down menu somewhere where you can “uncheck” unwanted categories.) Also, when you double-click the “Count of Race” label in the table you can specify exactly what type of counts should be shown and in which way it should be formatted. Try, for example, to get your counts to appear as percentage of the overall total.

You can now create a bar chart as usual, including or excluding the blank response as you see fit. We will later revisit the pivot table tool and investigate additional options and possibilities.