A histogram is a graph of a frequency distribution and is a…

A histogram is a graph of a frequency distribution and is a useful way to

give a picture summary of a set of data involving a quantitative variable. In

this activity, you will create and interpret histograms.

How is a histogram similar to a bar graph and how is it different from a

bar graph?

SSAc01.indd 396 6/19/06 7:54:07 PM

The following table gives the percentage of high school seniors who took the

SATs in the academic year ending in 2003 for a group of 14 southeastern

states and Washington DC.

State AL AR DE DC FL GA KY LA MD

Percent

Taking Test

10 6 73 77 61 66 13 8 68

State MS NC SC TN VA WV

Percent

Taking Test

4 68 59 14 71 20

What is the range of the "percent taking test" data shown in the table?

If you want to group the data into approximately six classes, what intervals

could you use?

Why is it important to group the data?

Why do you want to choose intervals of equal width?

SSAc01.indd 397 6/19/06 7:54:07 PM

Fill in the following table, and then construct, by hand, a histogram for the

percentage of high school seniors in the 15 southeastern locations who took

the SATs. Be sure to label the axes of your histogram.

Interval (percent taking SATs) Frequency (number of states in each interval)

Describe the overall shape of your histogram.

There are several ways to create a histogram using Excel. We'll illustrate the

first way using the SAT data for all states. First, import the "EA1.3.1 SAT data.

xls" file from the CD or website into an Excel worksheet.

SSAc01.indd 398 6/19/06 7:54:07 PM

One way to create histograms is to use the Chart wizard discussed previously.

The Chart wizard is designed to use with data that have been grouped-either

categorical data or quantitative data-into classes or intervals. To create a histogram

using the Chart wizard, you will group the data, which is not too difficult

if you first sort it.

You'll create a histogram of the "percent taking SATs" for all the states,

so first sort the data by this column; however, be sure to highlight all three

columns and then begin to perform the sort. From the menu bar, select

order.

Next decide what the "bins" will be. That is, you need to decide what classes

(of equal width) to use to group the data. For this data set, it appears that 0

< % <= 10; 10 < % <= 20; 20 < % <= 30, and so on will be convenient classes.

Use your sorted data to count the number of data values that fall into each

of these classes, and create a table in columns E and F of your Excel worksheet

with the class labels in column E and the frequencies for each class in

column F.

Use this table of classes and frequencies, and create a bar graph like you did

previously.

To make this completed bar graph look like a histogram, adjacent bars

should touch one another. To change the bar width, double-click on one of

the bars of the finished graph to access the Format data series dialog box.

Select the Options tab and change the Gap width from 150% to 0%. Then

click OK.

You may add titles and experiment with some of the other features of bar

graphs (like adding or taking away axis labels and so on).

SSAc01.indd 399 6/19/06 7:54:07 PM

Describe the overall shape of the SAT histogram for all states. How does this

histogram compare with the one you created by hand for the southeastern

states? Using the two histograms, what can you say about the percentage of

seniors taking SATs in the southeastern states and in all the states.

Here is another method to create a histogram with Excel. For this histogram,

you'll use a file containing data about the points scored by the winning team

in the Super Bowl for the years 1967 to 2003. Retrieve the "EA1.3.2 Super

Bowl.xls" file from the CD or website. You'll make a histogram of the variable

"points scored by winner."

You can also create a histogram using Excel's Analysis ToolPak, which groups

the data into classes for you.

The ToolPak may need to be installed; to check, choose Tools from the menu

bar and select Add-Ins. If the Analysis ToolPak box is not checked, select it

by clicking and then click OK. If it is already selected, just click OK. Now when

you choose Tools from the menu bar, you will see a Data analysis option.

From the menu bar choose Tools and then Data analysis; scroll down to

the Histogram option and click OK.

In the dialog box, type the reference for the range of your data, B1:B38 in

the Input range area, or click and drag from cell B1 to cell B38. Leave the

your data. Check the Labels box because B1 (where there is a label for the

SSAc01.indd 400 6/19/06 7:54:08 PM

to denote the upper left cell of the output range, and check the box Chart

output to put the histogram on the same sheet of the workbook as the

The output entries under Bin in the C column are the upper limits of the

boundaries for each class, and the corresponding frequencies appear in

cells in the D column. (Notice the numbers at the bottom of each bar in the

histogram.)

Adjacent bars should touch in a histogram. To change the bar width, doubleclick

one of the bars to bring up the Format data series dialog box. Select

the Options tab and change the Gap width from 150% to 0%. Then click OK.

To create a slightly different histogram using "bin" intervals that you

choose, type My Bins (or any other appropriate label) in cell E1. (You may

need to move your histogram.) Because the data values range from 14 to 55,

you will use intervals of width 5, beginning with the interval from 10 to 15.

So enter the values 15, 20, 25, 30, 35, 40, 45, 50, 55 in cells E2:E10. (This

creates the bins or classes of: 10 < points scored < 15; 15 < points scored

< 20; 20 < points scored < 25; and so on, to the last class, 50 < points scored

< 55.) Notice that the right endpoint of each interval or class is included in

the interval but the left endpoint is not.

Use the procedure for creating a histogram described previously, but type

One option is to get rid of the legend. To do this, click the legend (the word

click OK.

SSAc01.indd 401 6/19/06 7:54:08 PM

You can resize both the plot area (the gray region containing the histogram)

and the larger rectangle by clicking in the boundaries and dragging

the appropriate handles horizontally, vertically, or diagonally. Click outside

the chart to "deselect."

To change the chart title, click the title word Histogram. A rectangular gray

border with handles will surround the word. Click the word Histogram and

type an appropriate title. When finished, click outside the graph.

Click the words My Bins at the bottom of the chart and type in an appropriate

X-axis title.

The More interval with 0 counts shouldn't be there. In the workbook

cell containing the word More, change the label to 60. The histogram is

dynamically linked to the data and the label on the X-axis changes to 60.

the Format plot area dialog box. Press the None button for Border and

also press it for Area.

Explain what your histogram shows about points earned by Super Bowl winning

teams.

Retrieve the data set "EA1.3.3 Governors Salaries.xls" from the CD or website.

SSAc01.indd 402 6/19/06 7:54:08 PM

Choose appropriate classes and use Excel to create a histogram for the Governors'

salaries.

Explain why you picked the classes you did.

Explain what your histogram shows.

Create, by hand, a stemplot of the "points scored" from the "EA1.3.2 Super

Bowl.xls" data file.

How is this graph different from the histogram for the same variable you

created previously?

SSAc01.indd 403 6/19/06 7:54:08 PM

Which plot gives a better sketch of the data and why?

What information from the table is not reflected in your graphs?

This is from word and the best that I can produce --I understand if you can not answer based on the information given---thanks

SATs and the Super Bowl:

Creating and Interpreting

Histograms

A histogram is a graph of a frequency distribution and is a useful way to

give a picture summary of a set of data involving a quantitative variable. In

this activity, you will create and interpret histograms.

How is a histogram similar to a bar graph and how is it different from a

bar graph?

1.

SSAc01.indd 396 6/19/06 7:54:07 PM

The following table gives the percentage of high school seniors who took the

SATs in the academic year ending in 2003 for a group of 14 southeastern

states and Washington DC.

State AL AR DE DC FL GA KY LA MD

Percent

Taking Test

10 6 73 77 61 66 13 8 68

State MS NC SC TN VA WV

Percent

Taking Test

4 68 59 14 71 20

What is the range of the "percent taking test" data shown in the table?

If you want to group the data into approximately six classes, what intervals

could you use?

Why is it important to group the data?

Why do you want to choose intervals of equal width?

2.

3.

4.

5.

Activity 1.3: SATs and the Super Bowl 397

SSAc01.indd 397 6/19/06 7:54:07 PM

398 Excel Activities

Fill in the following table, and then construct, by hand, a histogram for the

percentage of high school seniors in the 15 southeastern locations who took

the SATs. Be sure to label the axes of your histogram.

Interval (percent taking SATs) Frequency (number of states in each interval)

Describe the overall shape of your histogram.

There are several ways to create a histogram using Excel. We'll illustrate the

first way using the SAT data for all states. First, import the "EA1.3.1 SAT data.

xls" file from the CD or website into an Excel worksheet.

6.

7.

8.

SSAc01.indd 398 6/19/06 7:54:07 PM

Instructions to Use Excel's Chart

Wizard to Create Histograms

One way to create histograms is to use the Chart wizard discussed previously.

The Chart wizard is designed to use with data that have been grouped-either

categorical data or quantitative data-into classes or intervals. To create a histogram

using the Chart wizard, you will group the data, which is not too difficult

if you first sort it.

You'll create a histogram of the "percent taking SATs" for all the states,

so first sort the data by this column; however, be sure to highlight all three

columns and then begin to perform the sort. From the menu bar, select

Data and Sort, and then sort by the appropriate column in ascending

order.

Next decide what the "bins" will be. That is, you need to decide what classes

(of equal width) to use to group the data. For this data set, it appears that 0

< % <= 10; 10 < % <= 20; 20 < % <= 30, and so on will be convenient classes.

Use your sorted data to count the number of data values that fall into each

of these classes, and create a table in columns E and F of your Excel worksheet

with the class labels in column E and the frequencies for each class in

column F.

Use this table of classes and frequencies, and create a bar graph like you did

previously.

To make this completed bar graph look like a histogram, adjacent bars

should touch one another. To change the bar width, double-click on one of

the bars of the finished graph to access the Format data series dialog box.

Select the Options tab and change the Gap width from 150% to 0%. Then

click OK.

You may add titles and experiment with some of the other features of bar

graphs (like adding or taking away axis labels and so on).

a.

b.

c.

d.

e.

Activity 1.3: SATs and the Super Bowl 399

SSAc01.indd 399 6/19/06 7:54:07 PM

400 Excel Activities

Describe the overall shape of the SAT histogram for all states. How does this

histogram compare with the one you created by hand for the southeastern

states? Using the two histograms, what can you say about the percentage of

seniors taking SATs in the southeastern states and in all the states.

Here is another method to create a histogram with Excel. For this histogram,

you'll use a file containing data about the points scored by the winning team

in the Super Bowl for the years 1967 to 2003. Retrieve the "EA1.3.2 Super

Bowl.xls" file from the CD or website. You'll make a histogram of the variable

"points scored by winner."

Instructions to Use Excel's Analysis

ToolPak to Create a Histogram

You can also create a histogram using Excel's Analysis ToolPak, which groups

the data into classes for you.

The ToolPak may need to be installed; to check, choose Tools from the menu

bar and select Add-Ins. If the Analysis ToolPak box is not checked, select it

by clicking and then click OK. If it is already selected, just click OK. Now when

you choose Tools from the menu bar, you will see a Data analysis option.

From the menu bar choose Tools and then Data analysis; scroll down to

the Histogram option and click OK.

In the dialog box, type the reference for the range of your data, B1:B38 in

the Input range area, or click and drag from cell B1 to cell B38. Leave the

Bin range field blank to allow Excel to select the bins (or groupings) for

your data. Check the Labels box because B1 (where there is a label for the

column) has been included in the Input range. Type C1 for Output range

9.

10.

a.

b.

SSAc01.indd 400 6/19/06 7:54:08 PM

to denote the upper left cell of the output range, and check the box Chart

output to put the histogram on the same sheet of the workbook as the

data. For now, you won't use the other options (Pareto and Cumulative

percentage.)

The output entries under Bin in the C column are the upper limits of the

boundaries for each class, and the corresponding frequencies appear in

cells in the D column. (Notice the numbers at the bottom of each bar in the

histogram.)

Adjacent bars should touch in a histogram. To change the bar width, doubleclick

one of the bars to bring up the Format data series dialog box. Select

the Options tab and change the Gap width from 150% to 0%. Then click OK.

To create a slightly different histogram using "bin" intervals that you

choose, type My Bins (or any other appropriate label) in cell E1. (You may

need to move your histogram.) Because the data values range from 14 to 55,

you will use intervals of width 5, beginning with the interval from 10 to 15.

So enter the values 15, 20, 25, 30, 35, 40, 45, 50, 55 in cells E2:E10. (This

creates the bins or classes of: 10 < points scored < 15; 15 < points scored

< 20; 20 < points scored < 25; and so on, to the last class, 50 < points scored

< 55.) Notice that the right endpoint of each interval or class is included in

the interval but the left endpoint is not.

Use the procedure for creating a histogram described previously, but type

E1:E10 in the Bin range area and type G1 for Output range.

Some Selected Enhancements to Your Histogram

One option is to get rid of the legend. To do this, click the legend (the word

frequency on the chart). From the menu bar, select Chart and then Chart

Options. Click the Legend tab and clear the Show legend box. Then

click OK.

c.

d.

e.

f.

a.

Activity 1.3: SATs and the Super Bowl 401

SSAc01.indd 401 6/19/06 7:54:08 PM

402 Excel Activities

You can resize both the plot area (the gray region containing the histogram)

and the larger rectangle by clicking in the boundaries and dragging

the appropriate handles horizontally, vertically, or diagonally. Click outside

the chart to "deselect."

To change the chart title, click the title word Histogram. A rectangular gray

border with handles will surround the word. Click the word Histogram and

type an appropriate title. When finished, click outside the graph.

Click the words My Bins at the bottom of the chart and type in an appropriate

X-axis title.

The More interval with 0 counts shouldn't be there. In the workbook

cell containing the word More, change the label to 60. The histogram is

dynamically linked to the data and the label on the X-axis changes to 60.

The histogram has a border around the Plot area and a gray shaded Plot

area. You can change this by double-clicking on the Plot area to bring up

the Format plot area dialog box. Press the None button for Border and

also press it for Area.

Explain what your histogram shows about points earned by Super Bowl winning

teams.

Additional Questions

Retrieve the data set "EA1.3.3 Governors Salaries.xls" from the CD or website.

b.

c.

d.

e.

f.

11.

1.

SSAc01.indd 402 6/19/06 7:54:08 PM

Choose appropriate classes and use Excel to create a histogram for the Governors'

salaries.

Explain why you picked the classes you did.

Explain what your histogram shows.

Create, by hand, a stemplot of the "points scored" from the "EA1.3.2 Super

Bowl.xls" data file.

How is this graph different from the histogram for the same variable you

created previously?

a.

b.

c.

2.

a.

Activity 1.3: SATs and the Super Bowl 403

SSAc01.indd 403 6/19/06 7:54:08 PM

404 Excel Activities

Which plot gives a better sketch of the data and why?

What information from the table is not reflected in your graphs