Ad Count Min/Hour 25 11.5 23 10.7 28 10.2 15 9.3 13 11.3 24 11 27 15 22 12 17 10 19 10.5 20 14.3 22 11.7 18 14.9 19 10.7 23 12.3 13 10.1 23 11.2 21...

FREQUENCY DISTRIBUTIONS AND HISTOGRAMS 

Excel’s Histogram dialog box is found under 

>Data>Data Analysis>Histogram (Note, the Data Analysis Toolpack must be turned on). 

Example 

Let’s make a histogram with four classes, using the data we stored in the workbook Book1ads (created in Hybrid Assignment 2 – This file is now available on Moodle with this assignment). Download the file from Moodle and open it. 

The number of ads per hour of TV is in Column A; we will represent these values in the histogram. We also need to specify class boundaries, and for this we will use Column C. The upper class boundaries for four classes are 16.5, 20.5, 24.5, and 28.5. 

Label Column C as Ad Count, and below the label enter the upper class boundaries from above, smallest to largest. The horizontal axis of the histogram will carry the label Ad Count. Note: Excel follows the convention that a data value is counted in a class if the value is less than or equal to the upper boundary (upper bin value) of the class. 

Now select 

>Data>Data Analysis>Histogram. Check the Labels option, and select New Workbook for the output option. Finally, check Chart output for the histogram, and check Cumulative percentage to produce an ogive. Note that you can select either both options or just one of them. Your dialog box should look similar to the one below. 

In the resulting worksheet, we moved and resized the chart window. Notice that the class boundaries are not shown directly under the tick marks. The first bar represents all the values less than or equal to 16.5, the second bar is for values greater than 16.5 and less than or equal to 20.5, and so on. There are no values above 28.5. 

If you do not specify the cells containing the bin range, Excel automatically creates enough bins (classes) to show the data distribution. 

Adjusting the Histogram 

Excel automatically supplies the "more" category (which will be empty if you specify upper class boundaries). To remove the "more" category, select the cells in the row of the frequency table that contains "more". Then, on the main menu bar select 

Home>Delete. Right-click on the chart area and click Select Data. Then press Cancel. This will update the chart to contain only data shown in the table. 

To make the bars touch, right-click on one of the bars of the histogram. Then click Format Data Series. Set Gap width to 0. Then press Close. 

The results should be similar to this: 

LAB ACTIVITIES FOR FREQUENCY DISTRIBUTIONS AND HISTOGRAMS 

1. The Book1ads workbook contains a second column of data, with the minutes of ads per hour of prime time 

TV. Retrieve the workbook again and use column B to make a histogram letting Excel determine the number of classes (bins). Save this Excel file as Question 1_Your Name and upload to Moodle. 

2. As a project for her finance class, Linda gathered data about the number of cash requests made between the hours of 6 P.M. and 11 P.M at an automatic teller machine located in the student center. She made a count every day for four weeks. The results follow. 

25 17 33 47 22 32 18 21 12 26 43 25 

19 27 26 29 39 12 19 27 10 15 21 20 32 24 17 181 

(a) Enter the data. 

(b) Create a histogram letting Excel determine the number of classes (bins). Save this Excel file as Question 2_Your Name and upload to Moodle. 

3. Histograms are not effective displays for some data. Consider the following data: 

1 2 3 6 7 9 8 4 12 10 

1 9 1 12 12 11 13 4 6 206 

Enter the data and make a histogram letting Excel do the scaling. Now drop the high value, 206, from the data set. Create a new histogram, again letting Excel do the scaling. Do you get more refined information from the histogram by eliminating the high and unusual data value? Save your Excel file as Question 3_Your Name and upload to Moodle.