Histogram Notes
Last revised on: Tuesday, January 19, 2010, 1033 words

Data File

The data file for this example is 02.15_HistogramTipsData.xls The mechanics are show on page 47 of the textbook. There is a video with a different example, currently named 02.20_HistogramConstruction_PriceQuote_Example02.02p59.avi.

What does a histogram show?

The idea of a histogram is to show the shape of the data as well as the most common values. It can give an idea of the dispersion, or how much variation is typical. Some data are skewed, typically to the right, meaning there are some extreme values larger than typical by no corresponding extreme values less than (to the left of) typical values.

Mechanics of making a histogram

Excel makes a histogram automatically. Click the Data tab, then “Data Analysis” then “Histogram”

[pic]

[pic]

Then point to the data column for the “Input Range”, Check Labels (if the first row has labels not data), and check “Chart Output”. Excel automatically calculates bin ranges so you can leave that box blank.

Below is the automatically created histogram for the variable “Tuition”. It is mathematically correct, but doesn’t look very good because of the “weird” numbers Excel has chosen for the bin limits. (A “bin” is the same thing as a “category” and is defined by a lower limit and an upper limit. Excel counts all data values that are strictly greater than the lower limit up to and including the upper limit. The count for the second bar from the left is 3, indicating that there were 3 schools with tuition more than $11,614 and up to and including $14,282.40.

[pic]

Making an effective histogram

If you want your histogram to make an effective exhibit in a report or presentation, it should “look good.” The key is to use “familiar” numbers for the bin endpoints. There are a few other points that should always be observed.

• All bins (categories) should have the same width (width = upper limit – lower limit)

• The first category should include the minimum value observed

• The largest category should include the maximum value observed

• Generally, there should be 5 to 15 bars or categories. Fewer than 5 doesn’t show enough detail. Using more than 15 bins can causes raggedness unless you have a large number of observations. The histogram above has 6 bins for 25 observations, an average of 4.25 counts per bin. I recommend having an average of at least 5 counts per bin to make a fairly smooth histogram. This suggests an upper limit of n/5 on the number of bins. Having 10 or more average counts per bin makes it even smoother, if you have an abundance of observations.

• The bin endpoints should be “familiar” numbers. No weirdness is allowed!

What numbers are “familiar”?

Is $14,282.40 a familiar number? Most business people would say not, so they would wonder why it was chosen as a bin endpoint. How about $10,000? Yes, that seems “familiar”. What about $1,000? $500? $333? $250? $200?

We need some rule for which numbers are familiar and which ones are not. Here is my suggestion. Take out some paper money. Those numbers are “familiar”. We have paper currency in denominations of 1, 2, 5, 10, 20, 50, and 100. We also have coins in denominations of 1, 5, 10, 25, 50, and 100 cents. So, that is my vote for the set of numbers that are “familiar.”

You can move the decimal point left or right and still have a familiar number. So the basic set is {1, 2, 2.5, 5}. Any power of ten multiple is also familiar, so 2,000, 200, 20, 2, 0.2, 0.02, 0.002, etc. would all be familiar numbers.

There are 2 rules that interact. The width should be a familiar number and the number of bins should be 5 to 15. Here are a couple of ways to find a familiar number for the width that will work. First, find the “range” which is the maximum value minus the minimum value. For the Tuition variable the range is $24,956 - $11,614 = $13,342. (This is the definition of “range” in statistics. In business, you would say, “the range is from $11,614 to $24,956”, giving the interval from the minimum to the maximum.) Dividing this range by 5 (the minimum number of bins) gives a (maximum) width of about $2,668. Dividing by 15 (the maximum number of bins) gives $889. So, what familiar numbers are between these? $2,500, $2000, and $1000. However, since there are only 25 observations, we should not have many more than 5 bins, so 15 is too many. Thus, the best choice would be either 2000 or 2500 for the width. I’ll choose 2000.

One more rule

The bin limits should be an integer multiple of the width. This is important in setting the limits for the first (smallest) bin. It would be mathematically correct for the first bin to be 11,613 to 13,613, but that defeats the purpose of having a nice number for the width. You could also have the first bin run from 11,000 to 13,000, which isn’t too bad, but the series of limits includes 17,000 and 19,000, which are not bad numbers, but are just not as much fun to look at.

If you let the first bin run from 5 times the width to 6 times the width, you get this sequence of boundaries: 10,000, 12,000, 14,000, 16,000, 18,000, 20,000, etc. Alternatively, you could have used 2500 as the width and had 10,000, 12,500, 15,000, 17,500, 20,000, etc. This lends a touch of elegance to your exhibits.

Automating the selection with Excel

The example file shows how to facilitate choices for bin limits. Here are the ranges for the five variables in the example file:

You can tabulate the width implied for 5 to 15 bins (see table to right).

For each variable, you can pick out a width close to a familiar number. My choices are highlighted, 0.050 for GPA (although 0.100 might be better), 5 for Acceptance Rate, $5,000 for Salary, 2 for percent employed, and $2,000 for Tuition.

The corresponding upper limits for the first (smallest) bin would be: 3.15, 10, 54,000, 84, and 12,000. The last bin limits would be 3.60, 45, 84,000, 100, and 26,000.